VBA Copying data from one workbook to another using lastrow ranges

Question

I have a button on a sheet with a PivotTable in a workbook called "Warranty Template.xlsm". I want this button to copy the first column of data, which starts at A5, and paste this column into another workbook called "QA Matrix Template.xlsm". I want the copied data to end at the last blank row of the column and I want the range in which it is pasted to also be pasted on the first blank row starting from D12 down.

Sub InsertData()


Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable")
  Set wsDest = Workbooks("QA Matrix Template.xlsm").Worksheets("Plant Sheet")

  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A5").End(xlUp).Row

  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "D12").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  wsCopy.Range("A5" & lCopyLastRow).Copy _
    wsDest.Range("D12" & lDestLastRow)

End Sub

I am getting Subscript Error: '1004' and I am not sure why. It has to do with my lCopyLastRow & lDestLastRow variables. The code works if I set static ranges but I need these ranges to be dynamic.

lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "D12").End(xlUp).Offset(1).Row Needs to have a column as the second argument, not a cell. Change "D12" to "D" and "A5" to "A" that should fix it — Warcupine
And wsCopy.Range("A5" & lCopyLastRow).Copy Will need to be "A" & lcopylastrow — Warcupine
I have changed "D12" to "D" and "A5" to "A" and no longer get an error. But no data is copied/pasted when I run the macro — aNobleNoob
There are empty cells above the data which starts at A5 from the data copied and D12 in the destination range so I'm not sure if this is what is causing the problem... — aNobleNoob

AAA AAA · Accepted Answer · 2019-07-10T14:07:35

Sub InsertData()

Dim wsCopy As Worksheet, wsDest As Worksheet
Dim lCopyLastRow As Long, lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable")
Set wsDest = Workbooks("QA Matrix Template.xlsm").Worksheets("Plant Sheet")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 1).End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, 4).End(xlUp).Offset(1,0).Row

'3. Copy & Paste Data
wsCopy.Range("A5:A" & lCopyLastRow).Copy _
wsDest.Range("D" & lDestLastRow)

End Sub

VBA Copying data from one workbook to another using lastrow ranges

1 Answers