Macro to copy a sheet and delete a macro button

  • Thread starter Thread starter JoeP
  • Start date Start date
J

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
 
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
 
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
 
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.
 
Brilliant. Thank you so much!!

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