insert row into multiple tables

MCC123

New Member
Joined
Apr 17, 2019
Messages
13
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
506
Office Version
365
Platform
Windows
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
 
Last edited:

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
Avoid selecting, it is super-slow!

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

MCC123

New Member
Joined
Apr 17, 2019
Messages
13
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

Active Member
Joined
Jun 4, 2015
Messages
442
Code:
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

New Member
Joined
Apr 17, 2019
Messages
13
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

Active Member
Joined
Jun 4, 2015
Messages
442
Did you try my latest code?
 

Paul Ked

Active Member
Joined
Jun 4, 2015
Messages
442
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

New Member
Joined
Apr 17, 2019
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,479
Messages
5,444,718
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top