Macro to Insert a # of rows in two different tables on different worksheets.

[Matt F]

I have a data table on Sheet1 and a table that will contain similar information on Sheet2 named Table 3. I need to ensure that when rows are inserted in the bottom of table 1 the same number of rows will also be added to the bottom of Table 3. Lastly I need to be sure that the formulas are carried down in each.

Currently the same number of rows is being added to Table 1 and Table 2. I don't know how to force the extra rows to skip over Table 2 and only insert the rows in Table 3.

Any help you can provide would be great. Here is what I have so far:

Sub InsertRows()
Dim RowNum As Integer
Dim LastR As Integer
Dim Tbl1 As Worksheet
Dim Tbl3 As Worksheet

On Error GoTo DoNoth:

RowNum = InputBox("How Many Rows to Insert?")

Set Tbl1 = ThisWorkbook.Sheets("Sheet1")
Set Tbl3 = ThisWorkbook.Sheets("Sheet2")
LastR = Tbl1.Cells(Rows.Count, 1).End(xlUp).Row


Tbl1.Activate
Rows(LastR & ":" & LastR + RowNum - 1).Select
Selection.Insert Shift:=xlDown
Tbl3.[Table3].Activate

With Tbl3

Rows(LastR & ":" & LastR + RowNum - 1).Select
Selection.Insert Shift:=xlDown
End With

Tbl1.Activate
Range("a1").Select

DoNoth:

End Sub

 

[Luke M]

Check out the below, with comments.

Sub InsertRows()
Dim RowNum As Integer

'Be careful with setting LastR as integer, as 2007+ worksheets
'can have more than 65536 rows, which is the limit of an integer
'But you should be okay in this setup
Dim LastR As Integer
Dim Tbl1 As Worksheet
Dim Tbl3 As Worksheet

On Error GoTo DoNoth:

RowNum = InputBox("How Many Rows to Insert?")

Set Tbl1 = ThisWorkbook.Sheets("Sheet1")
Set Tbl3 = ThisWorkbook.Sheets("Sheet2")

'When using With statement, every item that needs to
'trace back to the With object needs to be proceeded
'by a period, like the .Rows line
With Tbl1
    LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Rows(LastR & ":" & LastR + RowNum - 1).Insert Shift:=xlDown
End With

'You were not previously changing the LastR variable, so it
'was not the last row of this sheet
With Tbl3
    LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Rows(LastR & ":" & LastR + RowNum - 1).Insert Shift:=xlDown
End With

DoNoth:

End Sub