Templatizing A Worksheet

gaming_mouse

Board Regular
Joined
Apr 6, 2005
Messages
139
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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

Board Regular
Joined
Apr 6, 2005
Messages
139
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862

ADVERTISEMENT

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

Board Regular
Joined
Apr 6, 2005
Messages
139
RAM said:
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,082
Messages
5,570,097
Members
412,311
Latest member
Mozz
Top