Autofill data from a formula in a dynamic table

Copper Contributor



I believe there are similar topics like mine but as I am not that good with VBA, it is hard to adjust someone’s solution to my needs.

I have 2 different tables - the first one has id column and the column with random codes.

The second table has also an id column(A), some other columns and the last one (column E) is for vlookup of the random codes from the first table. I can autofill the records myself with no problem but the second table is dynamic and the amount of rows is always changing. I basically record a macro. So the vba script  breaks when the amount of rows in the second table is different. How can I make it autofill the formula in the table of any size?


I hope someone can assist. If you need more info, let me know!

Thank you!

2 Replies


Is your second table really a table, in the sense that it has been created by clicking Table on the Insert tab of the ribbon, or Format as Table on the Home tab of the ribbon?

If so, the VLOOKUP formula should automatically be propagated to new rows.


As for VBA: you could use code like this. You'll have to modify the name of the sheet.

    Dim LastRow As Long
    With Worksheets("Second Sheet")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("E2:E" & LastRow).Formula = "=VLOOKUP(A2,...)"
    End With

@Hans Vogelaar sorry, it is not a table, I meant that both are excel files with columns/rows :) thank you a lot, I will try your solution!