VBA to match number of rows in a secondary table to the primary table automatically

[LotsOfQuestions88]

[FONT=&quot]Hi![/FONT]
[FONT=&quot]I've been trying for ages (not good at VBA!) to solve this with no success - I'm sure it's very simple but I'm not winning![/FONT]
[FONT=&quot]I have a primary data table (Table1) in Sheet1 and a secondary table (Table28) on a different sheet, Sheet2. Table 28 is set up to pull through some of the column data from Table 1 if entries in a certain column in Table 1 meet a criteria. [/FONT]
[FONT=&quot]The problem I have is trying to make Table 28 expand as Table 1 expands so that they have the same number of rows as the data need to match relative row positions within the tables. I have tried the following ( Sheet3 C2 contains a formula which gives the total number of rows for Table 1 and H2 gives total number of rows for Table28 ) - the idea was to add a row if the total number of rows in Table28 are less then in Table1: [/FONT]
[FONT=&quot]Sub Table_add_row()
'
' Add row to bottom of table

With ActiveSheet.ListObjects("Table28").Range.Select

If Worksheets("Sheet3").Range("C2").Value > Worksheets("Sheet3").Range("H2").Value Then

Selection.ListObject.ListRows.Add AlwaysInsert:=False

Range("A1").Select

Else: Range("A1").Select

End If

End With

End Sub[/FONT]
[FONT=&quot]However, this always end with a runtime error 9, subscript out of range error.

[/FONT]
[FONT=&quot]Ideally, this would happen automatically rather than having to insert a button to trigger a macro.[/FONT]
[FONT=&quot]Does anyone have any suggestions? Many thanks in advance for your help![/FONT]
[FONT=&quot]BW[/FONT]

 

[Paul Ked]

That's good. you're welcome!