Trouble pasting last data entry row because of pivots table

Luke1690

Board Regular
Joined
Jul 26, 2022
Messages
121
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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?
 

Attachments

  • Capture.PNG
    Capture.PNG
    103.6 KB · Views: 15
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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.

Rich (BB code):
    '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:
Rich (BB code):
    'wsCopy.Range("d4:q4").Copy
    wsCopy.Range("d4:q" & lCopyLastRow).Copy

Let me know how you go.
 
Upvote 0
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.

Rich (BB code):
    '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:
Rich (BB code):
    'wsCopy.Range("d4:q4").Copy
    wsCopy.Range("d4:q" & lCopyLastRow).Copy

Let me know how you go.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top