insert row into multiple tables

[MCC123]

Hi all,
I have a worksheet that I set up with 47 tables I need to be able to insert a row above the last line of each of table and copy formulas, cond. form. but not values. I have a code but excel has to reference it 47 times and in turn it takes for ever to complete and it copies cell format but not formulas and Cond. Form. ....I was curious if there is an easier way? I am very new to VBA.....thanks in advance for any help.


here is the macro code I have assigned to a button

Sub Insert Row()

ThisWorkbook.Sheets("Sheet1").Range("Table1").Select
Rows(Selection.Row).Insert shift:=xlDown


this is repeated 46 times in the sub range.

ThisWorkbook.Sheets("Sheet1").Range("Table47").Select
Rows(Selection.Row).Insert shift:=xlDown

End Sub

 

[VBE313]

I am not by a computer but try this code and let me know.
Sub Insert Row()
dim i as long
i = 1
Do Until i = 46
Sheets(“Sheet1”).Range(“Table” & i).select
Rows(Selection.Row).Insert shift:=xlDown
i = i + 1
Loop
end sub

 

[Paul Ked]

Avoid selecting, it is super-slow!

Sub InsRow()
    Dim i&
    For i = 1 To 46
        Sheet1.Range("Table" & i).Rows.Insert shift:=xlDown
    Next
End Sub

 

[MCC123]

It gives me a run time error 1004 Application defined or object defined error

it highlights this row of the code....


Sheets("TSXX Report").range("TSXX01" & I).select


my table names are TSXX01 - TSXX47

the sheet name is TSXX Report


Thanks a bunch VBE313 for the quick response!

 

[Paul Ked]

Sub Ins()
    Dim i&
    For i = 1 To 9
        [COLOR=#333333]Sheets("TSXX Report")[/COLOR].Range("TSXX0" & i).Rows.Insert shift:=xlDown
    Next
    For i = 10 To 47
        [COLOR=#333333]Sheets("TSXX Report")[/COLOR].Range("TSXX" & i).Rows.Insert shift:=xlDown
    Next
End Sub

 

[MCC123]

It gives an error

Sub InsertRow()
Dim i&
For i = 1 To 46
TSXXReport.Range("TSXX01" & i).Rows.insert shift:=xlDown
Next
End Sub

This is how the code appears on my sheet.

TSXX01- 47 are my tble names and TSXX Report is the sheet name I have 8 sheets in the workbook but only need to refer to the TSXX Sheet.


Thx Paul Ked!

 

[Paul Ked]

Did you try my latest code?

 

[Paul Ked]

TSXXReport.Range("TSXX01" & i).Rows.insert shift:=xlDown

TSXXReport is that the code name of the sheet? If not, it will not recognise the sheet name. Unless you've changed the code names of the sheets it will be something like Sheet1, hence me putting Sheets("TSXX Report")...

What is "TSXX01" & i when i is 1? It's "TSXX011", no such table.

 

[MCC123]

It gives a run time error

can't move cells In a filtered range


Sub Ins()
Dim i&
For i = 1 To 9
Sheets("TSXX Report").Range("TSXX0" & i).Rows.insert shift:=xlDown this line is wha sets off the debugger.
Next
For i = 10 To 47
Sheets("TSXX Report").Range("TSXX" & i).Rows.insert shift:=xlDown
Next
End Sub

 

[Paul Ked]

Clear the filters.

Why the O and not 0?