|
Macro to copy a sheet and delete a macro button
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 |
Macro to copy a sheet and delete a macro button
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
Macro to copy a sheet and delete a macro button
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" wrote: 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 |
Macro to copy a sheet and delete a macro button
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
Macro to copy a sheet and delete a macro button
Hi Joe
Is it a Forms button ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
Macro to copy a sheet and delete a macro button
Yes it is.
"Ron de Bruin" wrote: Hi Joe Is it a Forms button ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
Macro to copy a sheet and delete a macro button
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" wrote in message ... Yes it is. "Ron de Bruin" wrote: Hi Joe Is it a Forms button ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
Macro to copy a sheet and delete a macro button
Brilliant. Thank you so much!!
"Ron de Bruin" wrote: 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" wrote in message ... Yes it is. "Ron de Bruin" wrote: Hi Joe Is it a Forms button ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
Macro to copy a sheet and delete a macro button
You are welcome
For more see this page http://www.rondebruin.nl/controlsobjectsworksheet.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... Brilliant. Thank you so much!! "Ron de Bruin" wrote: 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" wrote in message ... Yes it is. "Ron de Bruin" wrote: Hi Joe Is it a Forms button ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JoeP" wrote in message ... 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 |
| All times are GMT +1. The time now is 03:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com