Copying and Overwriting Data from one Table to Another on a Different Sheet VBA

[TylerDove]

Good afternoon,

After loads of googling I can't solve the problem I'm having.

I want to (as the title states) copy data from one table to another where the second table is on a separate sheet. In the case where there is already data in the second table, I want to wipe out the table and paste in the data from table 1.

As it stands, my code is working to clear table 2, but that's it.

Help please. Code is below. Thanks!

Sub testingsStuff()

Dim ws1 As Worksheet
Dim ws2 As Worksheet


Set ws1 = Sheets("Growing Location Editor")
Set ws2 = Sheets("growing_location_commits")

    source = "Table1"
    destination = "Table2"


'clearing contents of table 2
    ws2.Activate
    ActiveSheet.ListObjects(destination).DataBodyRange.Delete


'copy data from table 1
    ws1.Activate
    ActiveSheet.ListObjects(source).DataBodyRange.Copy


'paste data into table 2 - I know that .Paste isn't an option, but I kept it there to make clear what I want to do
    ws2.Activate
    ActiveSheet.ListObjects(destination).DataBodyRange.Cells(1, 1).Paste
       
End Sub

 

[My Aswer Is This]

Try this:

Sub Copy_Data()
'Modified  7/29/2018  9:41:06 PM  EDT
Range("Table2").Delete
Range("Table1").Copy Range("Table2")
End Sub

 

[TylerDove]

Hi @My Aswer Is This

Thank you for the reply!

When I run that code, however, I get the following error:
"Run-time error '1004';
Method 'Range' of object'_Worksheet' failed"

Why would that be?

 

[My Aswer Is This]

I always test my scripts.
I just ran it again and it worked for me.
I assume you have a Table named Table1 and a Table named Table2 both in the same Workbook
I'm using Excel 2013

Not sure why it would not work for you.

 

[tonyyy]

TylerDove,

You might consider the following...

Sub testingsStuff()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Source As ListObject, destination As ListObject
Dim lRows As Long, lColumns As Long

Set ws1 = Sheets("Growing Location Editor")
Set ws2 = Sheets("growing_location_commits")
Set Source = ws1.ListObjects("Table1")
Set destination = ws2.ListObjects("Table2")
lRows = Source.ListRows.Count
lColumns = Source.ListColumns.Count

'clearing contents of table 2
If Not destination.DataBodyRange Is Nothing Then destination.DataBodyRange.Delete

'copy data from table 1
    Source.DataBodyRange.Copy

'paste data into table 2 - I know that .Paste isn't an option, but I kept it there to make clear what I want to do
    ws2.Activate
    destination.InsertRowRange.Resize(lRows, lColumns).Activate
    ActiveSheet.Paste
End Sub

Cheers,

tonyyy