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?

 

[Kingofhearts6464]

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

 

[Kingofhearts6464]

Hi Zot

Ya it's a little hard to explain. Basically sheet1 through sheet32 are the same. Let's say column D represents Toys, column E represents Returns. Then each row from row 4 down to let's say 8 has a specific toy like TMNT action figure leo. The sales of each toy is tracked daily. Sheet 32 needs to add all 31 cells together and have the total show up on cell D4. We are constantly adding new toys so need new rows but the formula I'm using doesn't copy the formula when I add the new row to all the sheets.

 

[Kingofhearts6464]

Hi Zot

Ya it's a little hard to explain. Basically sheet1 through sheet32 are the same. Let's say column D represents Toys, column E represents Returns. Then each row from row 4 down to let's say 8 has a specific toy like TMNT action figure leo. The sales of each toy is tracked daily. Sheet 32 needs to add all 31 cells together and have the total show up on cell D4. We are constantly adding new toys so need new rows but the formula I'm using doesn't copy the formula when I add the new row to all the sheets.

So cell D4 on sheet32 would be the total for Tmnt Leo and cell E4 would be returns for Tmnt Leo.
If that makes sense

 

[Zot]

Not sure on which row you insert line, but you can use offset to copy the formula above inserted line and paste to new line.

After insert line on RowNum, this will copy from column D to M and past formula on added row
ws.Range("D" & RowNum, "M" & RowNum).Offset(-1, 0).Copy
ws.Range("D" & RowNum).PasteSpecial (xlPasteFormulas)

I'm on Excel 2016 but I think this is still valid.

 

[Kingofhearts6464]

That formula does copy the formulas but when I insert a new row in between two existing rows with tracking data in them it copies everything from that row not just the formulas. It's there a way to insert the rows but only copy the formulas and have all the cells blank?

 

[Zot]

That formula does copy the formulas but when I insert a new row in between two existing rows with tracking data in them it copies everything from that row not just the formulas. It's there a way to insert the rows but only copy the formulas and have all the cells blank?

Is it because the range you are copying not only contains formula but values as well?

Maybe you can do something like this if the between column D and M has both values and formula

Set rngTarget = ws.Range("K" & RowNum, "M" & RowNum)

rngTarget.Offset(-1, 0).Copy
rngTarget.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
rngTarget.SpecialCells(xlCellTypeConstants, 23).Clear

Not that I defined Target range to shortened the expression.

 

[Kingofhearts6464]

Thanks for your help. It's not working. I'm just going to scratch the idea. It so frustrating I feel so close but so far away from the goal lol.I'm thinking if I made one blank row at the bottom of all the sheets with only the formulas in it and then have the commandbutton copy it and add the new row right above it that should work just have to figure out the right code to keep the blank row the one that's always being copied because the rows will move each time and it's just not going to be in alphabetical order which is lame but they'll have to live with it

 

[Zot]

Thanks for your help. It's not working. I'm just going to scratch the idea. It so frustrating I feel so close but so far away from the goal lol.I'm thinking if I made one blank row at the bottom of all the sheets with only the formulas in it and then have the commandbutton copy it and add the new row right above it that should work just have to figure out the right code to keep the blank row the one that's always being copied because the rows will move each time and it's just not going to be in alphabetical order which is lame but they'll have to live with it

It was tested ok in my workbook. Just not sure how the formula looks like. I don't know if the formula refers to variable in the same row or to a fixed reference or something.

I think why not just write formula to the cell directly from VBA. Then no need to copy and paste