AutoNew Excel Macro Help Requested

[gigram]

Probably a pretty simple request, but I have spent hours crashing excel macros with no luck....

I am trying to create a macro that will format a tab (borders, shading, some text and formulas) when it is created. I have created a 'sample' tab that contains the exact format I would like for the new tabs. I thought I could have the new tab go to the sample tab, copy it all and paste it on the new tab. However, the macro seems to crash because it keeps looking for a specific sheet name (like sheet2 or sheet 3) and as I create new tabs these numbers increment (the sample tab name seems fine as its name stays the same).

Should I be using the AutoNew function? If so, how do I handle the moving between tabs to grab the data and then come back to the new tab, when the name of the new tab will constantly be incrementing.

Any help is MUCH appreciated!!! Thx!

 

[onlyadrafter]

In your macro, create a new tab, name it and then do the copying from your preset tab. You can then rename the tab after copying.

Hope this is of some use.

maybe only a drafter
but the best user here!

 

[onlyadrafter]

Hello again,

Another simpler option would be just to copy the entire tab, will this not be good enough?

maybe only a drafter
but the best user here!

 

[joerd]

You can also save your sample worksheet as a template (*.xlt), which should be stored (automatically) in your user templates directory. If you then right click on a sheet tab, and then choose Insert..., Excel should present you with all available templates.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.