Templatizing A Worksheet

[gaming_mouse]

I have a workbook which consists of a number of worksheets with identical columns, conditional formatting rules, and worksheet-level macros. I am continually updating the formatting rules and macros, and it is annoying to have to copy them over from when sheet to the next as I make changes, since I have 10 worksheets and plan to add more. Is there any way to templatize the worksheet, so that I only have to make changes to one worksheet, yet have them reflected in all worksheets?

Thanks,
gm

 

[gaming_mouse]

bump

 

[RAM]

Well, I’m no VBA pro, far from actually, but to get you rolling and from my simple viewpoint, and if I was to do what you're doing, I would store all macros in PERSONAL.XLS.

Then you can reach the macros and only have one set of them, not 10 or more, from any new or old worksheet. They all looks the same, right?

As I said I’m no pro on this and have no simple solution how you move the macros from your regular VBE to PERSONAL.XLS, but once there you run the macros from Alt+F8, choose PERSONAL.XLS from the drop down Macros in: and select the macro and Run.

Let's see what the other guys has to say. I see we have some good coders on the first page of Mr. Excel msg board at this time.

HTH

RAM

 

[gaming_mouse]

So how would I deal with conditional formatting? Would I have to write it by hand in VB and store it in a separate file? And then, yes, as you said, how would I actually tell each worksheet to use that file?

Thanks,
gm

 

[RAM]

Is the formatting stored in the macros for now or do you do it manually? If you do it manually select one sheet, hold down Ctrl and select all other sheets that look the same. Then do all changes in the formatting and all other sheets will update accordingly.

Only the open sheet will run the macro when you select it from Alt+F11, [Edit] but if the macro calls all the sheets then that's what it does.

Edit: It appears to me that if you use the above mentioned technique and select all sheets (you want to run the macro on) hit Alt+F11 and Run, that macro will run in these sheets only (as long the macro is written for one sheet only and doesn't call other sheets too.)

HTH

RAM

 

[gaming_mouse]

Is the formatting stored in the macros for now or do you do it manually? If you do it manually select one sheet, hold down Ctrl and select all other sheets that look the same. Then do all changes in the formatting and all other sheets will update accordingly.

Will this work for conditional formatting rules too, or just regular formatting?

 

[RAM]

For what I can see, and what you probably already have tried, after you select all worksheets, conditional formatting will be applied in all selected sheets and so will changes in existing formatting.

Is this what you need?

RAM

 

[RAM]

PS. The same technique will work also on all the different worksheets you want to print: Hold down Ctrl and click on all worksheets to be printed and Print.

RAM