VBA - Populate table values from another table with button click

Question

Hello,

Suppose I have two sheets: Source and Destination. Source sheet has two tables (one below another), Tbl1 and Tbl2. Destination sheet has two tables (one below another), Tbl3 and Tbl4.  

***The tables in Source sheet are DYNAMIC, meaning these tables will have rows added periodically. 

When I click a button, I want the entries in Source.Tbl1 to populate Destination.Tbl3 and Source.Tbl2 to populate Destination.Tbl4. In other words, I want to populate tables with values from other tables when I click a button.

Is there a way I can do this through VBA or some other means?

Any help is much appreciated!

Answer 1

Use:

Dim rng As Range

Dim strTable As String

Dim strAddress As String

Dim i As Long

With Worksheets("Source")

    For i = 1 To .ListObjects.Count

        strTable = .ListObjects(i).Name

        Set rng = .ListObjects(strTable).Range

        strAddress = rng.Cells(1, 1).Address

        rng.Copy Destination:=Worksheets("Destination").Range(strAddress)

        With Worksheets("Destination")

            .ListObjects(i).Name = strTable & "Copy"

        End With

    Next i

End With

Answer 2

Hi Doug,

That's perfect! You saved my entire week - thank you!!

Answer 3

Hi Doug,

WOW that definitely makes my life a whole lot easier!! Thank you so much! I do have a follow up question: is there any way I can name each of the destination tables in the VBA code itself? (it's fine if I have to name it one by one too)

If one table in Source sheet is called "DevTbl" I would like its copy in the Destination sheet to be called "DevTblCopy". Would this be possible?

Please let me know.

Answer 4

When I click a button, I want the entries in Source.Tbl1 to populate Destination.Tbl3 and Source.Tbl2 to populate Destination.Tbl4. In other words, I want to populate tables with values from other tables when I click a button.

Why? There's generally no reason to duplicate perfectly good information. If you're trying to repurpose it for reporting needs, then you might want to look at PivotTables.

Answer 5

Use the following code:

Dim rng As Range

Dim strTable As String

Dim strAddress As String

Dim i As Long

With Worksheets("Source")

    For i = 1 To .ListObjects.Count

        strTable = .ListObjects(i).Name

        Set rng = .ListObjects(strTable).Range

        strAddress = rng.Cells(1, 1).Address

        rng.Copy Destination:=Worksheets("Destination").Range(strAddress)

    Next i

End With