Copy a worksheet without copying the code

Question

I can copy a worksheet by calling its .Copy method.

Sheets("Example").Copy After:=Worksheets("Sheet3")

However, this also copies any macros or event handlers associated with that worksheet. How do I copy the worksheet without copying any Visual Basic code?

Answer 1

After copying the sheet, you can reference it by name and then delete all of the lines from the code module:

Sheets("Example").Copy After:=Sheets("Sheet3")

' Get the code/object name of the new sheet...
Dim strObjectName As String
strObjectName = ActiveSheet.CodeName

' Remove all lines from its code module...
With ThisWorkbook.VBProject.VBComponents(strObjectName).CodeModule
    .DeleteLines 1, .CountOfLines
End With

To work with project components, you'll need to ensure that the "Trust access to the VBA project object model" option is enabled in Excel's macro settings.

Answer 2

Create an empty worksheet and copy the contents of the original sheet over.

Sub Copy_Sheet_Without_Code(original_sheet As String, copied_sheet As String)
    ' Creates a copy of original_sheet without any VBA code associated with it
    ' The copy is named copied_sheet
    Sheets(original_sheet).Cells.Copy
    Sheets.Add.Name = copied_sheet
    Sheets(copied_sheet).Cells.PasteSpecial Paste:=xlPasteAll
End Sub