Excel Question #46

[Ghoulish Delight]

The quick and dirty way I can think of answering question 1 does involve a small amount of VBA knowledge, but not a lot. Depending on how large each sheet is, it could take some tedious initial setup.

But the basic steps are:
1) start recording a macro
2) copy a sheet to a new sheet
3) manually change everything you want to change
4) stop recording the macro
5) (this is the tricky part, and I'd need to do some learning myself to get it right) edit the macro.
5a) You should see that it's saying to copy specifically whichever sheet you recorded the macro from. Change it to whatever the syntax is that means "current sheet'.
5b) you'll see a line for each edit you made explicitly setting the cell to whatever you set it to. Change that to a formula that increments it from the same cell on the current sheet.

If you've got a mind to go that route, I can help you get steps 5a and 5b right later this evening.

[Kevy Baby]

First and foremost, thank you to GD and Alex for responding - I know I can always count on you to offer assistance on this.

And thank you to Betty for, well... thinking that way!

Originally Posted by Ghoulish Delight

I assume you want a solution that doesn't involve learning VBA?

Yes

However, I found a fairly painless solution to Question 1. I just made several copies of the last tab and did a replace in each tab from "14" (the row on the main tab that all of the data was in that pertained to that output tab) to "15" (or "16", etc.). Since most all of the info in the tabs is cell referencing and text, it was a safe thing to do.

Took about 15 minutes to create all the copy tabs, rename them all (there were 14 new tabs to create) and fix the cell references using search and replace.

Originally Posted by Alex

I'm not sure I understand the first question. Do you mean you want it, when you create a new worksheet that it will automatically know that cell A1 should have a certain formula in it?

Yes, that was the goal. But as noted above, I found an inelegant, albeit quick and painless solution.

Now, how about the second question? That one has me stumped.

[Drince88]

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

[Betty]

Originally Posted by Drince88

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

That's a great trick!

[Kevy Baby]

Originally Posted by Drince88

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)

Great tip - I shall try this tomorrow.

ETA: I just tested and it works!