VBA Code:
Sub test1()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Workbooks.Open "\\################################"
'Set variables for copy and destination sheets
Set wsCopy = Workbooks("test1.XLSM").Worksheets("sheet1")
Set wsDest = Workbooks("############.xlsm").Worksheets("data")
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "d").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, "D").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("d4:q4").Copy
wsDest.Range("d" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
This code seems to paste the copied row at the bottom of the blank pivot table, instead of at the top under the header.
Last edited by a moderator: