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?

score 6 · Answer 1 · answered Aug 14 '15 at 18:40

6

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

answered Aug 14 '15 at 18:40

MackM

2,906
5
31
45

3

Did you ask a question then answer it immediately yourself? O.o – findwindow Aug 14 '15 at 18:54
3

@findwindow When I can't find an answer to a question I have on Stack Overflow, I usually try to put it on here for anyone else in my shoes after I figure it out. See here: http://stackoverflow.com/help/self-answer – MackM Aug 14 '15 at 19:12
1

Right. I did it myself before. I just don't do it immediately after. I don't plan it XD It's a natural process. In fact, I dread I will have to be the one to answer my own last question XD – findwindow Aug 14 '15 at 19:15
1

pretty sure this method is slower and would raise more errors (like with invisible items) – Patrick Lepelletier Feb 17 '19 at 15:34

score 6 · Accepted Answer · edited Nov 07 '19 at 18:53

6

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.

edited Nov 07 '19 at 18:53

MackM

2,906
5
31
45

answered Aug 14 '15 at 18:51

Bond

16,071
6
30
53

Note that you need to give VBA permission to alter other VBA code to use this method. http://stackoverflow.com/questions/25638344/programmatic-access-to-visual-basic-project-is-not-trusted-excel – MackM Aug 14 '15 at 19:21
2

This raises errors when copying sheets with referenced libaries - the error occurs before removal of the offending code :9 – jamheadart Mar 12 '19 at 15:44
This also activates the Worksheet_Activate event – sirplus Sep 08 '22 at 08:52

score 4 · Answer 3 · answered Jul 24 '18 at 12:51

Not exactly what the OP wanted, but you can also remove the worksheet macros by copying the worksheet to a new workbook, and then saving that workbook using the following code (which ultimately saves the workbook as .xlsx and strips the code away) ...

ActiveWorkbook.SaveAs fileName:="yourfile.xlsx", fileFormat:=51

This has the benefit of persisting all the data and formatting (eg column widths), and doesn't require VBA object model permissions.

Of course, once saved, you could then reopen the saved workbook and move the sheet back into the original workbook, now without the code!

Application.DisplayAlerts = False must be used when additional user actions undesirable. More details are in my answer. — Sharunas Bielskis, Oct 01 '19 at 08:18

score 2 · Answer 4 · answered Oct 01 '19 at 08:15

I tried to use the code in one of the answers before

ActiveWorkbook.SaveAs fileName:="yourfile.xlsx", fileFormat:=51

but the program displayed alert and in this case user must manually react to this alert, so I added additional code lines:

    Application.DisplayAlerts = False
    With ActiveWorkbook
     .SaveAs Filename:=excelReportFilePath, FileFormat:=xlOpenXMLWorkbook ' this enumeration value is the same as 51
    End With
    Application.DisplayAlerts = True

In this case, code block worked without alert and after formed workbook with copied sheets in which originally were code was closed, and then opened again from its directory, in the copied sheets the code is absent. In this case alert - Programmatic access to Visual Basic Project is not trusted - wasn't thrown as can be on some user PC as in the case when code

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

was used. So the advantage of this way that you don't need to care about security settings in a user PC and about correct copying data from source sheet into the additionally created empty sheet.

score 1 · Answer 5 · answered Feb 17 '22 at 14:54

Expanding on some of the answers above.

I was trying to save a copy without the code to attach to an email. However, you cannot specify file type with ActiveWorkbook.SaveCopyAs.

By specifying the file extension .xlsx in the filename: ActiveWorkook.SaveCopyAs fileName:="my_name.xlsx" the file will save but then throw an error when it's opened about file type mis-match. Also, when attaching to an email mine got caught in spam filters due to the file type mis-match.

To get around this I ended up saving a copy, opening the copy, opening it to save with the .xlsx format, and then attaching that copy to the email.

  ActiveWorkbook.Save
  ActiveWorkbook.SaveCopyAs fileName:=tmpFile     
        
  'save as .xlsx with no macros, openiong and saving will throw errors, turn off alerts
   Application.DisplayAlerts = False
   With Application.Workbooks.Open(tmpFile)
     .SaveAs fileName:=Replace(tmpFile, ".xlsm", ".xlsx"), FileFormat:=51
     .Close
   End With
   Application.DisplayAlerts = True

Copy a worksheet without copying the code

5 Answers5

Linked