Copying Sheet without buttons

[Microsoft Communities]

Is there a way to copy a sheet except for the buttons.
I have 1 sheet with five command buttons and want to copy the sheet to a new
one with only one button not all five.
This currently done with a macro. But it copies all five buttons.

 

[Dave Peterson]

How about copying the sheet with all 5 buttons and then delete the buttons you
don't want.

If they're from the Control Toolbox toolbar and you know the names:

Worksheets("Sheet1").Copy _
after:=Worksheets("sheet1")

With ActiveSheet 'the newly copied sheet
.CommandButton2.Cut
.CommandButton3.Cut
.CommandButton4.Cut
.CommandButton5.Cut
End With

If they're from the Forms toolbar and you know the name:

Worksheets("Sheet1").Copy _
after:=Worksheets("sheet1")

With ActiveSheet 'the newly copied sheet
.Buttons("Button 2").Delete
.Buttons("Button 3").Delete
.Buttons("Button 4").Delete
.Buttons("Button 5").Delete
End With

 

[Microsoft Communities]

Thanks again Dave
Works great.

How about copying the sheet with all 5 buttons and then delete the buttons
you
don't want.

If they're from the Control Toolbox toolbar and you know the names:

Worksheets("Sheet1").Copy _
after:=Worksheets("sheet1")

With ActiveSheet 'the newly copied sheet
.CommandButton2.Cut
.CommandButton3.Cut
.CommandButton4.Cut
.CommandButton5.Cut
End With

If they're from the Forms toolbar and you know the name:

Worksheets("Sheet1").Copy _
after:=Worksheets("sheet1")

With ActiveSheet 'the newly copied sheet
.Buttons("Button 2").Delete
.Buttons("Button 3").Delete
.Buttons("Button 4").Delete
.Buttons("Button 5").Delete
End With

 

[FloMM2]

You could also go into "Control Builder", Select the button that you do not
want to copy (in you case all, one at a time). Select the button, right click
the button, select "PrintObject" and change it to No.

hth

Thanks again Dave
Works great.

 

[Dave Peterson]

That didn't work for me.

The button was still copied to the new sheet.

You could also go into "Control Builder", Select the button that you do not
want to copy (in you case all, one at a time). Select the button, right click
the button, select "PrintObject" and change it to No.

hth

 

[Microsoft Communities]

I have had them set that way and all that does is stop them from printing.

You could also go into "Control Builder", Select the button that you do
not
want to copy (in you case all, one at a time). Select the button, right
click
the button, select "PrintObject" and change it to No.

hth