Copying Sheet without buttons

  • Thread starter Thread starter Microsoft Communities
  • Start date Start date
M

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.
 
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
 
Thanks again Dave
Works great.


Dave Peterson said:
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
Click to expand...
 
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 said:
Thanks again Dave
Works great.
Click to expand...
 
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
Click to expand...
 
I have had them set that way and all that does is stop them from printing.

FloMM2 said:
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
Click to expand...
 
Back
Top