Inserting a row in multiple worksheets

[kevin316]

Here is what I want to do.

I have a workbook that consists of 15 worksheets. I want to be able to click a button to add a row to sheet one and have it added in the other sheets in the exact same place. To complicate it a bit I want it to add it in the same place on the sheet each time (between rows 10 & 11)

I did find a post on how to add a row to sheet one but it adds multiple rows to sheet two with special colors and formulas. I just need it to work like when you insert a row normally. (format and formulas)

[Leith Ross]

Hello Kevin316,

Welcome to the Forum!

Call this macro from your button.

Please Login or Register  to view this content.

[kevin316]

Thanks for the quick reply.

I just changed the "ActiveCell" to "Rows (11)" and now it inserts a row after row 10 on all sheets.

Thanks again. I look forward to learning lots from this forum!!

[Leith Ross]

Heloo Kevin,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[kevin316]

Found one problem. It doesn't bring down the formula with it.

[Leith Ross]

Hello Kevin,

Do you mean the formula above the inserted line?

Add this to the code...

Please Login or Register  to view this content.

[kevin316]

The formula and borders from the line above.

Cell C1 would be "=A1+B1"
Cell C2 would be "=A2+B2"

If you inserted a row between cell C1 and C2, C2 would go to C3 and continue the formula.

Simple task, just not sure how to automate it.

[kevin316]

sorry missed the bottom part of your post, I will try that

[kevin316]

Here is what I have. Now it is doing both, inserting two line one with the formulas and one without. I tried playing around with it but couldn't get anywhere.

Please Login or Register  to view this content.

[Leith Ross]

Hello Kevin,

I don't know what is wrong. I ran a test with the code I posted last and all the data and formulas were copied down from row 10 to row 11. Can you post your workbook?

[kevin316]

Here ya go. I'm not sure if I put that last piece of code in the right place.

When I add a line to Frac 2 it should add the same line to sheet 1 along with the formulas and formatting.

I noticed something else as well. I have a values sheet that I use to populate the dropdowns in the rest of the work book and when I add lines it adds them to that sheet as well (thats what I wanted it to do). It creates a problem because it shifts the menue items. Is there a way I can exclude this sheet from the auto line insert?

Thanks for all your help

[Leith Ross]

Hello Kevin,

It is easy to exclude the one worksheet from the loop. I am not sure I understand what you mean...

When I add a line to Frac 2 it should add the same line to sheet 1 along with the formulas and formatting.

I read this a copying and pasting the row from "Frac 2" to "Sheet1" and not filling down to the inserted row on "Sheet1". Which is it?

[Leith Ross]

Hello Kevin,

I have it sorted out. The main sheet and the value sheets are excluded from the loop. A new row is inserted at row 11 on each remaining sheet in the loop. The data and formulas are copied from line 10 and then any cells without formulas are cleared. Here is the macro which has been added to the attached workbook. Click the button and the macro does the rest.

Please Login or Register  to view this content.

[kevin316]

Sorry, trying to be clear.

I have did some more work so hopefully it will be a bit clearer

I want to insert a blank row into "master" that is complete with the formulas and at the same time add a line to sheets "Frac 1" through Frac 14", complete with formulas and exclude the "Values" sheet from adding a line.

So to sum it up, by hitting the "Add Line" button located on the "master" sheet a row would be added to all sheets other than "Values". That row would have to fill the formulas as required on that sheet and it will be in the same location on each sheet (row 11)

Right now it is adding 2 rows, 11 and 12. 11 does have formulas in it but they are not in the correct sequence (they are linked to row 13, you will see what I mean) and row 12 is blank.

[kevin316]

Button was missing

[Leith Ross]

Hello Kevin,

See post #13. We posted at the same time. I have attached the updated workbook.

[kevin316]

GREAT! That got it. I just had to delete the Frac 2 reference and it does just what I want it to.

Thank you very much

[kevin316]

well I thought that had it? Everything is working except when I add a line the master sheet formulas go line 10, line 12 and line 12. 11 is missing. It's working on the other sheets though.

[Leith Ross]

Hello Kevin,

Since the master is dependent on all the other sheets formulae, it needs to be changed last. I have made that change in the attached workbook.

[kevin316]

Now the master sheet is working but the other sheets are doing what the master was going, skipping row 11 in the sequence and using row 12 twice.

Thanks for your help and patients with this. I like to think I am good with excel but when it comes to programming it is way over my head.

[Leith Ross]

Hello Kevin,

Set the workbooks Calculation Mode to Manual. Go to Tools > Options... > Calcualtion (tab) > Manual (option button). Mixing VBA and worksheet formulas generally isn't a good idea because of these types of problems. Let me know what happens.

[kevin316]

Nope, same thing. Do you think it may be because it uses links both ways, a loop? If it changes on thing first that works but the others don't and it seems to do that both ways.

[Leith Ross]

Hello Kevin,

Generally, a master list is updated separately from other lists that are dependent on them. I am not sure this design can be made to work. The changing of the master list and all the subordinates lists simultaneously creates a kind of circular reference with the formulas. While you are still early in your design phase, you should map what you want to accomplish and restructure the project accordingly. Ask questions of others before you pursue a new course of action. It will save you a lot of time and trouble.