Macro to copy a sheet and delete a macro button

[JoeP]

In Excel 2003 I have a worksheet that has a macro button on it. I would like
a macro that will do two things.
1. Make a copy of the sheet into a new workbook.
2. Delete the macro button on the newly created worksheet in the new book
and keep the macro button on the original worksheet.

Any help would be appreciated. Thanks.

JoeP

 

[Ron de Bruin]

Basic code looks like this

Sub test()
Dim sh As Worksheet

ActiveSheet.Copy
Set sh = ActiveSheet

On Error Resume Next
sh.DrawingObjects.Visible = True
sh.DrawingObjects.Delete
On Error GoTo 0
End Sub

 

[Mike H]

Hi,

I'm not sure under which circumstances the button would actually be copied.
try this

Sub Button1_Click()
Dim WB As Workbook
ActiveWorkbook.Sheets("Sheet1").UsedRange.Copy
Set WB = Workbooks.Add
WB.Sheets(1).Range("A1").PasteSpecial
End Sub


Mike

 

[JoeP]

Ron,
Thanks for your help. Your code does what I asked for. The sheet that I want
to copy also contains a text box that I want to keep when copied. Is there
anyway to just delete the macro button and not all drawing objects. Thanks
again for your help.

 

[Ron de Bruin]

Hi Joe

Is it a Forms button ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[JoeP]

Yes it is.

Hi Joe

Is it a Forms button ?

 

[Ron de Bruin]

Use this then

Sub test2()
Dim sh As Worksheet

ActiveSheet.Copy
Set sh = ActiveSheet

On Error Resume Next
sh.Buttons.Delete
On Error GoTo 0
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[JoeP]

Brilliant. Thank you so much!!

Use this then

Sub test2()
Dim sh As Worksheet

ActiveSheet.Copy
Set sh = ActiveSheet

On Error Resume Next
sh.Buttons.Delete
On Error GoTo 0
End Sub

 

[Ron de Bruin]

You are welcome

For more see this page
http://www.rondebruin.nl/controlsobjectsworksheet.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm