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]

Try this in place of your code above:

    If Worksheets("Sheet3").Range("C2").Value > Worksheets("Sheet3").Range("H2").Value Then
        Sheet2.ListObjects("Table28").ListRows.Add AlwaysInsert:=False
    End If
    Range("A1").Select
End Sub

 

[Paul Ked]

For Auto-Run, you can trigger this on an event such as Sheet1 change like:

Sheet1 Code:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Ked
End Sub

Module code:

Sub Ked()
    If Worksheets("Sheet3").Range("C2").Value > Worksheets("Sheet3").Range("H2").Value Then
        Sheet2.ListObjects("Table28").ListRows.Add AlwaysInsert:=False
    End If
    Range("A1").Select
End Sub

ps You don't need the Range("A1").Select

 

[LotsOfQuestions88]

Hi Paul,

Thank you so much for your help! I've put the code into Sheet1 and a module as you have it, but when I type in the next row under Table1 (ie adding another row to Table1) I get the same runtime 9 , subscript out of range message and it highlights this line:

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

Any idea what might be going wrong?
The formula in Sheet3 C2 and H2 are:

=ROWS(Table1[#All])

and

=ROWS(Table28[#All])

which output numbers.

Many thanks again!

 

[Paul Ked]

I can't understand why, I've tried it using your formulas in C2 & H2 and it's working fine for me. Let me have a minute on this.

 

[Paul Ked]

Stripped down to the bone

Sub Ked()
    If Sheet3.Range("C2") > Sheet3.Range("H2") Then
        Sheet2.ListObjects("Table28").ListRows.Add
    End If
End Sub

works for me too.

What values have you got (on mouseover) when the code breaks?

 

[LotsOfQuestions88]

Do you mean the values in C2 and H2?
If so, I've got 18 and 10 respectively (ie secondary table is smaller than primary when starting code)

 

[LotsOfQuestions88]

PS The tables also have different numbers of columns as Table28 will have different data added to some of the columns manually. Might that be causing an issue?

 

[Paul Ked]

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.

They should be equal??

Although it doesn't make any difference, if I make my tables to match yours, as soon as I add a new line to Table1 then Table28 gets updated to match the number of lines.

The columns don't make a difference as we are looking at rows.

I'm sorry, I'm stumped!

Can you upload your workbook? Or email me paulked at gmail dot com.

 

[LotsOfQuestions88]

Hi Paul,

After fiddling with things and checking names / references etc, I've just tried your stripped-back version - and it works!!

I don't understand why the first version doesn't as I can't see any major difference (?) but very happy that this one does - many thanks for your time!

Best wishes