Copy tables to another worksheet

Question

I have 2 tables, "Table1" and "Table2" in Worksheet(1).

I would like to copy these tables to another worksheet(2) where the top left corner of Table1 is in the new worksheet at cell "A1" and the Top left corner cell of Table2 is in the new worksheet at cell "O1."

Unless the ActiveCell is A1 in Sheet2, the tables won't copy and paste correctly. Am I not activating a sheet correctly?

Sub CopyTables()
Worksheets(2).Activate          'must be on ExHouStorm or Worksheet(1)
Worksheets(1).ListObjects("Table1").Range.Copy
Worksheets(2).Paste


Worksheets(1).ListObjects("Table2").Range.Copy
Worksheets(2).Range("O1").Select
Worksheets(2).Paste

End Sub

This seems pretty simple as it partially works but I think I have a syntax mistake.

score 8 · Accepted Answer · edited May 23 '17 at 10:26

8

You should be able to copy directly by providing the destination.

Sub CopyTables()

    Worksheets(1).ListObjects("Table1").Range.Copy _
        Destination:=Worksheets(2).Range("A1")


    Worksheets(1).ListObjects("Table2").Range.Copy _
        Destination:=Worksheets(2).Range("O1")

End Sub

By directly and explicitly addressing the objects and their destinations, you can avoid .Activate and .Select altogether.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

edited May 23 '17 at 10:26

Community

1
1

answered Jul 29 '15 at 14:18

This works perfectly. Just that adding to the scenario, in my case I need to copy a table from one worksheet to another without knowing the table name (list object name). How to do that if in your answer, we aren't aware of the name Table1 or Table2? – Meet May 24 '21 at 10:19

score 2 · Answer 2 · edited Aug 23 '17 at 10:29

2

Another method, simpler and shorter:

Range("Table1[#All]").copy Range("L1")

edited Aug 23 '17 at 10:29

Cody Gray

230,875
49
477
553

answered Aug 23 '17 at 08:58

Ankush agarwal

31
1

Copy tables to another worksheet

2 Answers2

Linked