Using the commandbutton to add a row to multiple sheets

[Kingofhearts6464]

Hello please help. My company recently fired our tech guy and needed someone to fill his shoes for a bit. I volunteered and have no idea why lol.

They have a daily spreadsheet with 31 sheets used to track sales of items per day. They would like a easy botton on sheet1 that would add a row to all the sheets in the same place. I've been able to create a commandbutton and I can now create a row in sheet1 but I cant figure out how to make it form a row in all 31 sheets at once. Can someone help with the code I would use?

 

[Zot]

Slight modification to what I found ?

Sub InsertRowAllSheets()
     
'  Thanks to firefytr for the code that has been adapted into this routine

Dim y As Integer
Dim cs As String
Dim r As Range
Dim ws As Worksheet

cs = ActiveSheet.Name
    
y = Application.InputBox("Enter the row number you wish to add", Type:=1)
If MsgBox("Are you sure you wish to insert at row " & y & " for ALL sheets?", _
    vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub
Application.ScreenUpdating = False
    
For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    Set r = ActiveSheet.Range("A" & y)
    Range("A" & y).EntireRow.Insert
Next ws
Sheets(cs).Activate
Application.ScreenUpdating = True

End Sub

 

[vcoolio]

Hello KoH,

Another option:-

Sub Test()

Dim ws As Worksheet

    For Each ws In Worksheets
          ws.Rows(1).EntireRow.Insert
    Next ws

End Sub

This inserts a new row in each sheet at A1. Depending on where you want to add the new row, just change Rows(1) to suit.

I hope that this helps.

Cheerio,
vcoolio.

 

[Kingofhearts6464]

Hello KoH,

Another option:-

Sub Test()

Dim ws As Worksheet

    For Each ws In Worksheets
          ws.Rows(1).EntireRow.Insert
    Next ws

End Sub

This inserts a new row in each sheet at A1. Depending on where you want to add the new row, just change Rows(1) to suit.

I hope that this helps.

Cheerio,
vcoolio.

That worked great thank you. I forgot that theres a worksheet at the end of the 31 sheets that I dont want to add rows to. Is there a way to only do a range of worksheets or minus specific worksheet from this command? Again thank you for your help

 

[vcoolio]

Hello KoH,

A slight modification as follows should do the trick:-

Sub Test()

Dim ws As Worksheet

      For Each ws In Worksheets
            If ws.Name <> "Whatever" Then  'Change sheet name "Whatever" to suit.
                 ws.Rows(1).EntireRow.Insert
            End If
      Next ws

End Sub

Just change "Whatever" to the sheet name that needs to be excluded.

I hope that this helps.

Cheerio,
vcoolio.

 

[Kingofhearts6464]

Hello KoH,

A slight modification as follows should do the trick:-

Sub Test()

Dim ws As Worksheet

      For Each ws In Worksheets
            If ws.Name <> "Whatever" Then  'Change sheet name "Whatever" to suit.
                 ws.Rows(1).EntireRow.Insert
            End If
      Next ws

End Sub

Just change "Whatever" to the sheet name that needs to be excluded.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio
This code is working great thank you.
Now they want a button that will delete a specific row from all sheets but again besides the "whatever" sheet.
I've been trying this code out but I can only get it to delete the row on the first sheet. Any ideas?

Private Sub CommandButton2_Click()

Dim ws As Worksheet
Dim RowNum As Integer

For Each ws In Worksheets
RowNum = Application.InputBox(Prompt:="Enter row number to be deleted")
ws.Rows(RowNum).Delete

Next ws

End Sub

 

[vcoolio]

Hello KoH,

Moving the RowNum variable declaration outside the For/Next loop then adding an If/End If statement to exclude the "Whatever" sheet should be all that you need:-

Private Sub CommandButton2_Click()

        Dim ws As Worksheet
        Dim RowNum As Integer
        RowNum = Application.InputBox(Prompt:="Enter row number to be deleted")
        
        For Each ws In Worksheets
                If ws.Name <> "Master" Then
                       ws.Rows(RowNum).Delete
                End If
        Next ws

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Kingofhearts6464]

Works great thank you for all your help. I'll definitely remember not to volunteer for something a know so little about. Although I did learn some new stuff so I guess theres a silver lining.

 

[Kingofhearts6464]

I feel bad asking another question since you've been so helpful already but I'm going to push my luck.
The formula I thought was working actually isnt. When I add a row to all sheets with the commandbutton it's not copying the formula like it does if you just right click and insert a row into the sheet.

I think I need a formula or code that would add up each cell on sheet1 through sheet31 and total it on sheet32 automatically. Right now I have each cell with a formula =Sum('1:31'!D4) on D4 and so on.
This formula does the job i need it to but when i add a row I have to manually go in and add the formula for that new row.

The area that needs the formula is D4 to M8 on sheet32 only.

Any ideas on how to accomplish this ?

 

[Zot]

I feel bad asking another question since you've been so helpful already but I'm going to push my luck.
The formula I thought was working actually isnt. When I add a row to all sheets with the commandbutton it's not copying the formula like it does if you just right click and insert a row into the sheet.

I think I need a formula or code that would add up each cell on sheet1 through sheet31 and total it on sheet32 automatically. Right now I have each cell with a formula =Sum('1:31'!D4) on D4 and so on.
This formula does the job i need it to but when i add a row I have to manually go in and add the formula for that new row.

The area that needs the formula is D4 to M8 on sheet32 only.

Any ideas on how to accomplish this ?

Just add a line to vcoolio code

For Each ws In Worksheets
If ws.Name <> "Whatever" Then  'Change sheet name "Whatever" to suit.
                 ws.Rows(1).EntireRow.Insert
            End If
      Next ws

Oopps. accidentally replied when need more clarification ?

You meant column D to M have formula and they are the same according to column from row 4 to row 8? I cannot imagine only these rows since you are adding and deleting rows