0

i have a table("table1") in one sheet.i have a button that adds the data in the table to another table in another sheet("table").every time its overriding the data. i want to add the data("table1") to the table("table2") instead of overriding it. this is the code:

 Sheets("Letter (P V)").ListObjects("Table1").Range.Copy _
      Destination:=Sheets("Actual").Range("E2")

thanks!

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
Leonid Zaydel
  • 1
  • 1
  • 3
    find the last row in the target range and add it there: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba – Scott Craner Dec 23 '19 at 15:35
  • 1
    Does the `Actual` sheet also hold a `ListObject`? If so then you can just get ahold of that table and get a reference to a new row with `ListObject.ListRows.Add`, which returns the `ListRow` object for the row that's been added at the bottom of the table; pasting to that row's `Range` should "just work". – Mathieu Guindon Dec 23 '19 at 16:00

1 Answers1

0

I think you want something like this

Dim ws As Worksheet
Dim rangeToPaste As Range
Set ws = Sheets("Actual")
'go up from the bottom to the first row populated in column 5(E) after do +1 to paste after the last row'
Set rangeToPaste = ws.Range("E" & ws.Cells(ws.rows.Count, 5).End(xlUp).row + 1)
Sheets("Letter (P V)").ListObjects("Table1").Range.Copy Destination:=rangeToPaste
hiichaki
  • 834
  • 9
  • 19