Duplicate CommandButton macros

[Blue Hornet]

I have a macro that I use to insert a new Worksheet into an existing
file. It sets up the new sheet with a common header and footer to
duplicate its neighbors, and adds some common navigation buttons (one
CommandButton and three AutoShapes with attached macros).

The AutoShapes are pretty simple. Each one has its own macro attached,
and when I copy and paste from my source AutoShape to my new target
worksheet the macro reference comes along, and everything works fine.

But the CommandButtons function differently. The macro code for them
is stored in each individual worksheet, and each button has specific
code attached which doesn't import with the cut and paste operation.

I suppose that I could replace the CommandButton objects with similar
AutoShapes and the macros will come along with each copy / paste, but
I'd like to understand how this is working and how I can control it via
macro. So my questions are:

1. Is there a way to copy a CommandButton object in such a way that it
brings its code along, OR

2. Is there a way that I can insert a new CommandButton object (copy
of an existing one) which will have its own unique number or name, and
yet still refer to code from another button?

3. How can I programmatically copy all of the "This Worksheet" code
from sheet to sheet? I tried to record a macro of that that I'd be
able to edit, but nothing recorded.

Thanks.

 

[Tom Ogilvy]

copy the sheet instead of the button or make a template sheet and copy that.
have the "this worksheet" code already in the tmeplate sheet or the sheet
your are copying. .

 

[Tom Ogilvy]

The more complex way would be to use VBA to write the code in the sheet
module

http://www.cpearson.com/excel/vbe.htm

that would assume your code has been granted security rights to do that.

 

[Dave Peterson]

Maybe you could use the Button from the Forms toolbar. It works very similarly
to the autoshapes.

Or instead of copying cells, you could copy the sheet (Edit|move or copy sheet).

Or insert a worksheet based on a template:

sheets.add type:="C:\myfolder\mytemplate.xlt"

So that the code/buttons/header/footers will be brought in all at once.

 

[Blue Hornet]

Maybe you could use the Button from the Forms toolbar. It works very similarly
to the autoshapes.

Or instead of copying cells, you could copy the sheet (Edit|move or copy sheet).

Or insert a worksheet based on a template:

sheets.add type:="C:\myfolder\mytemplate.xlt"

So that the code/buttons/header/footers will be brought in all at once.

Tom and Dave,

Thank you both. The 'duplicate sheet' idea was of course the best way
to work this. And so simple that I kick myself for not having realized
it.

Chris