Trouble pasting last data entry row because of pivots table

[Luke1690]

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.

 

[Alex Blakenburg]

The reference to a Pivot Table seems inconsistent with the rest of your code ?
Do you want to give us some visibility over what you mean ? Where is it copying it and where do you want it to copy it ? Ideally an XL2BB but at least a picture for starters.

Other points:
• Your comments for 1 & 2 refer to column A but you are in fact using Column D. Is Column D correct ?
• You are getting "lCopyLastRow" but not using it, your copy line has row 4 hard coded in it.

 

[Luke1690]

The reference to a Pivot Table seems inconsistent with the rest of your code ?
Do you want to give us some visibility over what you mean ? Where is it copying it and where do you want it to copy it ? Ideally an XL2BB but at least a picture for starters.

Other points:
• Your comments for 1 & 2 refer to column A but you are in fact using Column D. Is Column D correct ?
• You are getting "lCopyLastRow" but not using it, your copy line has row 4 hard coded in it.

• Sorry i am using Column D that is correct.
• That was going to be my next question i can get it to copy 1 line of data across but i need it to copy all data up to the last data entry row, in Columns D:Q.

As you can see in the picture it seems to paste it at the bottom of the pivot table adding a new line each time. When i need it to paste underneath the last data entry.


Thankyou i hope this makes more sense?

 

[Alex Blakenburg]

I don't believe that is a pivot table but just a regular Excel Table.
If you click inside the table do you get a TAB on the far right that says Table Design ?
If you click on that TAB on the far left what is the name in the Table Name box ?

 

[Luke1690]

I don't believe that is a pivot table but just a regular Excel Table.
If you click inside the table do you get a TAB on the far right that says Table Design ?
If you click on that TAB on the far left what is the name in the Table Name box ?

Sorry for being an idiot, you are right that is only the data source for the pivot table.

 

[Alex Blakenburg]

OK a couple of changes.
1) Last Row of table
When you go xlup on a table, it goes to the last row of the table, you want it to go to the last "empty" row of the table.
So make the change below.
You didn't get back to me on the Table name so you will need to change Table1 to your table name.

    'lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "D").End(xlUp).Offset(1).Row
    lDestLastRow = wsDest.Range("Table1").Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row

2) to make your copy go to the last row make this change:

    'wsCopy.Range("d4:q4").Copy
    wsCopy.Range("d4:q" & lCopyLastRow).Copy

Let me know how you go.

 

[Luke1690]

Sorry for being an idiot, you are right that is only the data source for the pivot table.

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "p").End(xlUp).Row


lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row


'wsCopy.Range("M6:U" & lCopyLastRow).copy wsDest.Range("B" & lDestLastRow)
wsCopy.Range("p6:AC" & lCopyLastRow).Copy
wsDest.Range("B" & lDestLastRow).PasteSpecial Paste:=xlPasteValues


i use the above to copy 1 line of data from one sheet to another and paste it under the last entered data entry.
it works fine.

im

OK a couple of changes.
1) Last Row of table
When you go xlup on a table, it goes to the last row of the table, you want it to go to the last "empty" row of the table.
So make the change below.
You didn't get back to me on the Table name so you will need to change Table1 to your table name.

    'lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "D").End(xlUp).Offset(1).Row
    lDestLastRow = wsDest.Range("Table1").Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row

2) to make your copy go to the last row make this change:

    'wsCopy.Range("d4:q4").Copy
    wsCopy.Range("d4:q" & lCopyLastRow).Copy

Let me know how you go.

 

[Luke1690]

Thankyou i will try this ASAP and get back to you.