Insert rows across all worksheets

[Hnk927]

I’m very inexperienced at vba and looking for some help.

I have a worksheet with 28 tabs. I’d like to insert a row on the first tab and have the action repeated across all other tabs. At the sam time, I would like to duplicate the formulas from a set range in the preceding row into this new row across all tabs.

I would also like the ability to delete a row in the first tab and have the action automatically duplicated across all other tabs

I’ve seen similar stuff but could not figure it to my situation. Thanks in advance for any help.

 

[arthurbr]

While holding the "Shift button down", click the first and the last sheet tab. All tabs are now highlighted.
You can now insert or delete rows, the action will be repeated on all sheets
To duplicate formulas use Excel tables

 

[mohadin]

Hi
VBA?
Try

Sub test()
Dim i As Long
Dim x As String
Dim z As Variant
    For i = 1 To Sheets.Count
        x = x & """" & Sheets(i).Name & """" & ","
    Next
    z = Split(Mid(Left(x, Len(x) - 2), 2), """,""")
    Sheets(z).Select
    Sheets("Sheet1").Rows("1:4").Insert Shift:=xlDown
End Sub

 

[Hnk927]

Hi
VBA?
Try

Sub test()
Dim i As Long
Dim x As String
Dim z As Variant
    For i = 1 To Sheets.Count
        x = x & """" & Sheets(i).Name & """" & ","
    Next
    z = Split(Mid(Left(x, Len(x) - 2), 2), """,""")
    Sheets(z).Select
    Sheets("Sheet1").Rows("1:4").Insert Shift:=xlDown
End Sub

I copy an pasted but it did not work. Perhaps I am doing something wrong.

 

[Hnk927]

While holding the "Shift button down", click the first and the last sheet tab. All tabs are now highlighted.
You can now insert or delete rows, the action will be repeated on all sheets
To duplicate formulas use Excel tables

Thanks for your reply. I was aware of this method, but am looking for a quick way of doing this where I can choose a row on my first sheet, insert a row and have this simultaneously happen in the same place over the other sheets. Then have the formulas from Columns R through AD from above row copied into new row across all sheets.

 

[Hnk927]

If I use the macro recorder to accomplish what I want to do, it produces the following which works. However, I'd like to modify it to have the macro apply to all sheets within the workbook without having them named within the macro, as I may change the names of the individual sheets. Currently as it is recorded, if I change a sheet name, then the macro does not work as intended. I am not really familiar with VBA but essentially want an 'Apply to all sheets'. I appreciate any clear help. Thanks.

Sub Insert_New_Row()
'
' Insert_New_Row Macro
'
' Keyboard Shortcut: Ctrl+i
'

Sheets(Array("Parameters", "Summary", "Pay Period 1", "Pay Period 2", _
        "Pay Period 3", "Pay Period 4", "Pay Period 5", "Pay Period 6", "Pay Period 7", _
        "Pay Period 8", "Pay Period 9", "Pay Period 10", "Pay Period 11", "Pay Period 12", _
        "Pay Period 13", "Pay Period 14", "Pay Period 15", "Pay Period 16", "Pay Period 17" _
        , "Pay Period 18", "Pay Period 19", "Pay Period 20", "Pay Period 21", _
        "Pay Period 22", "Pay Period 23")).Select
    Sheets("Parameters").Activate
    Sheets(Array("Pay Period 24", "Pay Period 25", "Pay Period 26")).Select Replace _
        :=False
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Rows(ActiveCell.Row).Select
    Selection.Insert Shift:=xlDown
    Sheets("Parameters").Select

Exit Sub

End Sub

 

[mohadin]

Hi,
Try

Sub Insert_New_Row()
'
' Insert_New_Row Macro
'
' Keyboard Shortcut: Ctrl+i
'

    Dim i As Long
    Dim x As String
    Dim z As Variant
    For i = 1 To Sheets.Count
        x = x & """" & Sheets(i).Name & """" & ","
    Next
    z = Split(Mid(Left(x, Len(x) - 2), 2), """,""")
    Sheets(z).Select
    Sheets("Parameters").Activate
    Sheets(Array("Pay Period 24", "Pay Period 25", "Pay Period 26")).Select Replace _
                                                                            :=False
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Rows(ActiveCell.Row).Select
    Selection.Insert Shift:=xlDown
    Sheets("Parameters").Select
End Sub

 

[Hnk927]

Hi,
Try

Sub Insert_New_Row()
'
' Insert_New_Row Macro
'
' Keyboard Shortcut: Ctrl+i
'

    Dim i As Long
    Dim x As String
    Dim z As Variant
    For i = 1 To Sheets.Count
        x = x & """" & Sheets(i).Name & """" & ","
    Next
    z = Split(Mid(Left(x, Len(x) - 2), 2), """,""")
    Sheets(z).Select
    Sheets("Parameters").Activate
    Sheets(Array("Pay Period 24", "Pay Period 25", "Pay Period 26")).Select Replace _
                                                                            :=False
    Rows(ActiveCell.Row).Select
    Selection.Copy
    Rows(ActiveCell.Row).Select
    Selection.Insert Shift:=xlDown
    Sheets("Parameters").Select
End Sub

Thanks, but only inserted a row on first sheet.

 

[mohadin]

Tested in here
works Ok.!!!!

 

[Hnk927]

Thanks, but only inserted a row on first sheet.

My bad, I had a typo that had not noticed. Fixed it and works now. Thank you for your help.