VBA - retrieve data on last create named range from previous inserted named range

[karl2022]

Hello Everyone,

I need a big help from you..

I have 3 named table : Table11, Table34, Table41.

The idea is that, any time i am inserting/adding a new table(lets say Table56), in the new table i need to add the data from previous inserted added table data: the values from columns Tax1, Tax2, Fees.

Sometimes the table might have additional columns and rows, which it shouldn't affect retrieving data from previous table into new table. Also, it might happen that the name of tables to be different, because the file is accessed by different users

The name of table is added as list in another worksheet when the table is created, for this i was able to create a small code using a template table (copy paste).



So, is it possible to retrieve data in last named table from previous inserted named table?



Anyone can give me an helpful hand?

 

[igold]

Hi karl2022- Welcome to the MrExcel Forum.
This code assumes that you have already created the new table named as the last Table name on the Sheet "Tables" (in Column "B") and will copy the values from the second to last Table name on that sheet. It also assumes that the new Table already has Headers.

Sub FillTable()

    Dim wsR As Worksheet: Set wsR = Worksheets("Report")
    Dim wsT As Worksheet: Set wsT = Worksheets("Tables")
    Dim tbl1 As ListObject, tbl2 As ListObject, r As Long, c As Long
    Dim dbr

    Set tbl1 = wsR.ListObjects(wsT.Cells(Rows.Count, 2).End(xlUp).Offset(-1, 0).Value)
    Set tbl2 = wsR.ListObjects(wsT.Cells(Rows.Count, 2).End(xlUp).Value)
    dbr = tbl1.DataBodyRange
    For r = 1 To tbl1.ListRows.Count
        For c = 1 To tbl1.ListColumns.Count
            tbl2.DataBodyRange(r, c) = dbr(r, c)
        Next
    Next

End Sub

 

[karl2022]

Hi karl2022- Welcome to the MrExcel Forum.
This code assumes that you have already created the new table named as the last Table name on the Sheet "Tables" (in Column "B") and will copy the values from the second to last Table name on that sheet. It also assumes that the new Table already has Headers.

Sub FillTable()

    Dim wsR As Worksheet: Set wsR = Worksheets("Report")
    Dim wsT As Worksheet: Set wsT = Worksheets("Tables")
    Dim tbl1 As ListObject, tbl2 As ListObject, r As Long, c As Long
    Dim dbr

    Set tbl1 = wsR.ListObjects(wsT.Cells(Rows.Count, 2).End(xlUp).Offset(-1, 0).Value)
    Set tbl2 = wsR.ListObjects(wsT.Cells(Rows.Count, 2).End(xlUp).Value)
    dbr = tbl1.DataBodyRange
    For r = 1 To tbl1.ListRows.Count
        For c = 1 To tbl1.ListColumns.Count
            tbl2.DataBodyRange(r, c) = dbr(r, c)
        Next
    Next

End Sub

Hello Igold,
Much appreciated your quick answer.

I think i didn't explain very well. Based on the ID# from NEW inserted table, somehow the code has to retrieve from PREVIOUS table the info based on ID# ( I guess also it is needed to match the columns' names) just from columns WAGE, TAX1,TAX2, FEES and add into NEW table, but in columns WAGE PREVIOUS, TAX1 PREVIOUS, TAX2 PREVIOUS, FEES PREVIOUS (to compare current amounts with previous amounts), as i tried to draw in the attached, so the result wont have any data for ID#658......
Could you please help?

Thank you again...

 

[igold]

How about this...

Sub FillTable()

    Dim wsR As Worksheet: Set wsR = Worksheets("Report")
    Dim wsT As Worksheet: Set wsT = Worksheets("Tables")
    Dim tbl1 As ListObject, tbl2 As ListObject, r As Long, c As Long
    Dim w2 As Long, K As Long
    Dim hdr, hdr2, dbr, dbr2

    Set tbl1 = wsR.ListObjects(wsT.Cells(Rows.Count, 2).End(xlUp).Offset(-1, 0).Value)
    Set tbl2 = wsR.ListObjects(wsT.Cells(Rows.Count, 2).End(xlUp).Value)
    dbr = tbl1.DataBodyRange
    dbr2 = tbl2.DataBodyRange
    hdr = tbl1.HeaderRowRange
    hdr2 = tbl2.HeaderRowRange
  
    For w2 = 1 To UBound(hdr2, 2)
        If UCase(hdr2(1, w2)) = UCase("Wage Previous") Then Exit For
    Next
    For r = 1 To UBound(dbr)
        For K = 1 To UBound(dbr2)
            If dbr(r, 1) = dbr2(K, 1) Then
                For c = 1 To 4
                    tbl2.DataBodyRange(K, c - 1 + w2) = dbr(r, c - 1 + 4)
                Next
                Exit For
            End If
        Next
    Next

End Sub

 

[karl2022]

wow, wow....igold THANKS ALOT ...It is working just perfect. the big steak from me ...and a bucket of......anything you would like.

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback!

Please mark the post that answered your question as the solution to help future readers. Little check mark icon on the right side of post.