Thread: macro to copy worksheet which has command buttons

I want to create a macro to make a copy of a worksheet on which there are command buttons.

I am having trouble with the command buttons.

I can record a macro that opens the control toolbox (it seems to be necessary to be in design mode), selects the sheet to be copied, inserts a new worksheet, and then pastes the selection into the new sheet.

Whilst recording the macro everything works fine, including the copying of the command buttons.

However, when I run the macro the command buttons are not copied.

Hi ParryNZ

Thank you for directing me to the Copy Method - I am surprised that I had not managed to find it myself. I am using Excel 2000 but the example you quoted is exactly as in the 2000 Help.

[Added later in case anyone follows this route]: I tried it out on a completely new workbook and it worked fine; so I have a problem in my original workbook with sheet names. [end of comment]

However, I am getting a runtime error '9': subscript out of range. I have checked the "sheet 1" name is correct (including copying and pasting from my workbook tab) so don't know what I am doing wrong. There hardly seems to be room for error.

I should have mentioned that I realize that I can of course copy a sheet by right clicking on the sheet tab, but I got to where I am by an indirect route and became puzzled by the behavior of the command buttons. Sometimes they don't copy, sometimes the stub of the procedure copies, sometimes the stub + body of the procedure is copied. Try as I might to be consistent in what I do, I can't recreate the behavior with certainty.

Thank you very much for your help. I feel I should be able to solve it from here ...

John

Originally Posted by parrynz

See the Copy method. The following example copies Sheet1 and pastes it as a new sheet (including buttons) after Sheet3.

Sub CopySheet()
   Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
End Sub

here is a piece of code i use to do what you are asking. create a module and then call the code from the command button.

Sub SheetTest()
Dim Sheetname As String
Sheetname = InputBox("Please enter new sheet name")
If Sheetname = "" Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.Copy Sheets(Sheets.count)
ActiveSheet.Name = Sheetname

 ' // add this piece if you do not want your command buttons to copy over \\ '
 'Dim oOle As OLEObject
  'For Each oOle In ActiveSheet.OLEObjects
   'If TypeName(oOle.Object) = "CommandButton" Then oOle.Delete
   'Next oOle

Application.ScreenUpdating = True
End Sub

what it does is asks you what you want to name your new sheet so you do not end up with "sheetxcopy" and instead you gain a real sheet name, then creates it for you, keeping all your formatting in place. If you add the piece i have marked in the code, it will remove the command buttons when you create a copy.