Mirroring Worksheets from a Master Template

[bbbfalcs]

Having done some research I think I can make it more clear what I hope to do, please lend some advice, all is welcome

I have a workbook that will have multiple sheets that I wish to have the exact same formatting, however, the data on each sheet may vary.

I want to be able to use a "Master Sheet" to make all formatting (ie. cell size, text alignment, font size/style) changes and have the other sheets automatically update.

I do not want this to affect or interfere with the data.


The reason is that some workbooks may end up with a large amount of worksheets and when I want to make a change to cell sizing or alignment I don't want to have to go through all of them to do this.

Again, TIA

[JBeaucaire]

Title suggestion:

Mirroring Worksheets from a Master Template

[shg]

We'll wait while you change the title ...

[bbbfalcs]

Thanks fellas...

Still trying to find the right solution to this one. Any help is appreciated!

[bbbfalcs]

Anyone?


Bueller?

[JBeaucaire]

First of all, that was VERY funny! Thanks for making my day.

I didn't have a suggestion, but you made me laugh so I thought about it and the only thing I can think of is that if you select multiple sheets at once, called GROUPING, and then make changes to one sheet, the changes you are making are being applied to all the sheets. So I thought maybe we could fake this.

So, on your master sheet (let's presume it's Sheet1), right-click the sheet tab and select VIEW CODE and put in this code:

Please Login or Register  to view this content.

Edit the array to list ALL the sheets that you want to be grouped this way, be sure to include the template (Sheet1?) in the list. Now as you make changes, it is updating the other sheets, too.

Then, when you switch to another sheet, the DEACTIVATE macro will ungroup the sheets and you will be back in individual sheet mode.

Hope this helps.

[bbbfalcs]

That sounds like were on the right track however I have to admit that you are dealing with someone completely unfamiliar with this VBA stuff.

I have been doing some reading the past day or two but I am certainly not educated on it.


So... after I put in that code and modified the array to meet my needs I tried to save, assuming that should be my next step.


When I tried to save a dialog box popped up with the following message:

"The following features cannot be save in macro-free workbooks:

VB projects

To save a file with these features, Click NO, and then choose a macro-enabled file type from the file type list

To continue saving as a macro-free workbook click Yes."



I fear this indicates that I have not set up my workbook appropriately for this kind of action but I have no idea how to remedy this one either! ha

please help?
(this project is becoming time-sensitive for me by now)

[JBeaucaire]

You're using Excel 2007. You can't use macros in a file saved as *.xlsx, you will have to save it as *.xlsm which proves for certain you meant to have macros. One of Excel 2007's "security" features.

[bbbfalcs]

Beautiful... That explains why it also popped up the Save As window

I might be getting somewhere now

[bbbfalcs]

JBeaucaire,

Your method works for me. It isn't exactly what I had in mind but it certainly seems to work well.

One thing, when I click back to the "template" page after clicking through the other sheets to confirm the changes were successful I get a pop up message that says:

"Run-time error '9':

Subscript out of range"

and my options are End, Debug, Help


When I click Debug it takes me to the code and has this line highlighted in yellow:

"Sheets(Array("Sheet Format Template", "Sheet2", "Sheet1")).Select"




Any insight into this? It does not seem to be affecting anything but it certainly would be annoying if I have to clear it every time I click the worksheet that is being used as the template!

Thanks again

[JBeaucaire]

That didn't happen to me, but I'll be happy to glance at your sheet.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[bbbfalcs]

Got the pop up message to stop.

I had deleted "Sheet1" and left it in the code. Once I deleted it from the code the pop up stopped.

Thank you for offering your help though, and especially for getting me this far!



I am still fairly surprised that there isn't a efficient way to have multiple worksheets draw their format and data positioning from a "master" worksheet. I have put together a combination of methods to do what I need but it sure would be nice to have it programmed to be automatic!!


I am attaching a sample spreadsheet for reference. The ticket is that I want to be able to modify things like cell size, data positioning, and arrangement on the "Sheet Format Template" worksheet and have these changes automatically reflected on "Sheet2", WITHOUT having any impact on the "data" on each worksheet.

[JBeaucaire]

Glad I could offer some help!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[Ghostwheel]

Can a wild card be used for the Sheets So that all sheets named T1 T2 T3 could be done by using a T* command in the macro?