I have a command button that executes a macro when clicked.
I have another macro that makes a copy of my template sheet.
If I manually make a copy of the template sheet, the button copies along
with the attached macro. If I used the macro to make a copy, the button is
copied with the text "Button 2" and does not bring the attached macro with it.
When I recorded the macro for copying the sheet, I used the same steps that
I use when doing it manually.
What am I doing wrong?

TIA

Are you copying the entire sheet (like edit|move or copy sheet) or are you
selecting a range and then copy|pasting that range?

It may be easiest to copy the entire sheet. Then the commandbutton and all the
code behind the worksheet will travel with the copy.

BABs wrote:
>
> I have a command button that executes a macro when clicked.
> I have another macro that makes a copy of my template sheet.
> If I manually make a copy of the template sheet, the button copies along
> with the attached macro. If I used the macro to make a copy, the button is
> copied with the text "Button 2" and does not bring the attached macro with it.
> When I recorded the macro for copying the sheet, I used the same steps that
> I use when doing it manually.
> What am I doing wrong?
>
> TIA

--

Dave Peterson

Dave,
Thx for the reply.
If I right click on the sheet tab and do "move or copy" then "create copy"
for the sheet, it copies the macro with the button. Life is good.
I wanted to automate this process because I repeat it often.
I clicked "record a macro", then followed the same steps, right click on the
tab, "move or copy", create a copy, then stopped recording.
I set the macro for Ctrl+shift+c.
If I hit ctrl+shift+c, it creates a copy of the sheet (calls it sheet (2)),
but the button does not have the macro attached to it and now shows the text
"button 2", instead of the "Balance yield" on the original button.
Same procedure as I see it, just different products.
Any ideas?






"Dave Peterson" wrote:

> Are you copying the entire sheet (like edit|move or copy sheet) or are you
> selecting a range and then copy|pasting that range?
>
> It may be easiest to copy the entire sheet. Then the commandbutton and all the
> code behind the worksheet will travel with the copy.
>
> BABs wrote:
> >
> > I have a command button that executes a macro when clicked.
> > I have another macro that makes a copy of my template sheet.
> > If I manually make a copy of the template sheet, the button copies along
> > with the attached macro. If I used the macro to make a copy, the button is
> > copied with the text "Button 2" and does not bring the attached macro with it.
> > When I recorded the macro for copying the sheet, I used the same steps that
> > I use when doing it manually.
> > What am I doing wrong?
> >
> > TIA
>
> --
>
> Dave Peterson
>

If your template is file type .xlt, why not just use SaveAs to make a copy?
It would take everything with it.

"BABs" wrote:

> I have a command button that executes a macro when clicked.
> I have another macro that makes a copy of my template sheet.
> If I manually make a copy of the template sheet, the button copies along
> with the attached macro. If I used the macro to make a copy, the button is
> copied with the text "Button 2" and does not bring the attached macro with it.
> When I recorded the macro for copying the sheet, I used the same steps that
> I use when doing it manually.
> What am I doing wrong?
>
> TIA

The template is just the name of a worksheet in my workbook. I leave that
sheet with only my formulas on it. I make a copy of it and then enter
numbers into the formulas for calculation on the copy.
It seems like the easiest way for me????????






"JLGWhiz" wrote:

> If your template is file type .xlt, why not just use SaveAs to make a copy?
> It would take everything with it.
>
> "BABs" wrote:
>
> > I have a command button that executes a macro when clicked.
> > I have another macro that makes a copy of my template sheet.
> > If I manually make a copy of the template sheet, the button copies along
> > with the attached macro. If I used the macro to make a copy, the button is
> > copied with the text "Button 2" and does not bring the attached macro with it.
> > When I recorded the macro for copying the sheet, I used the same steps that
> > I use when doing it manually.
> > What am I doing wrong?
> >
> > TIA

Are you sure you used a commandbutton from the control toolbox toolbar?

It sounds like you used a button from the Forms toolbar. Those buttons have
macros (in a general module) assigned to them.

If you use that commandbutton from the control toolbox toolbar, you'll find that
life gets much simpler. You will have to move the code into the
Commandbutton1_click event (in the worksheet module), though.

BABs wrote:
>
> Dave,
> Thx for the reply.
> If I right click on the sheet tab and do "move or copy" then "create copy"
> for the sheet, it copies the macro with the button. Life is good.
> I wanted to automate this process because I repeat it often.
> I clicked "record a macro", then followed the same steps, right click on the
> tab, "move or copy", create a copy, then stopped recording.
> I set the macro for Ctrl+shift+c.
> If I hit ctrl+shift+c, it creates a copy of the sheet (calls it sheet (2)),
> but the button does not have the macro attached to it and now shows the text
> "button 2", instead of the "Balance yield" on the original button.
> Same procedure as I see it, just different products.
> Any ideas?
>
> "Dave Peterson" wrote:
>
> > Are you copying the entire sheet (like edit|move or copy sheet) or are you
> > selecting a range and then copy|pasting that range?
> >
> > It may be easiest to copy the entire sheet. Then the commandbutton and all the
> > code behind the worksheet will travel with the copy.
> >
> > BABs wrote:
> > >
> > > I have a command button that executes a macro when clicked.
> > > I have another macro that makes a copy of my template sheet.
> > > If I manually make a copy of the template sheet, the button copies along
> > > with the attached macro. If I used the macro to make a copy, the button is
> > > copied with the text "Button 2" and does not bring the attached macro with it.
> > > When I recorded the macro for copying the sheet, I used the same steps that
> > > I use when doing it manually.
> > > What am I doing wrong?
> > >
> > > TIA
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson