CODEWITHSUNDEEP
vbaexcel
MackM
MackM

Reputation: 3022

Copy a worksheet without copying the code

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?

Upvotes: 10

Views: 25980

Answers (6)

MackM
MackM

Reputation: 3022

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

Peter Rosenberg's answer improves this by only copying used cells.

Upvotes: 8

Peter Rosenberg
Peter Rosenberg

Reputation: 21

Further expanding, on the observations I had, using 64bit (whether it matters or not) Excel 365 VBA. Doing what MackM suggested, in my usecase:

    Sheets(original_sheet).Cells.Copy
    Sheets.Add.Name = copied_sheet
    Sheets(copied_sheet).Cells.PasteSpecial Paste:=xlPasteAll

caused the original_sheet (of limited size), to become copied into copied_sheet of maximum size, after a warning prompt of my Cut&Paste buffer was huge and if I wanted to keep it for use (i said yes). So it did its job, but I care for saving space for unused cells. So, in instead I did like this, in the first statement:

    Sheets(original_sheet).UsedRange.Cells.Copy

So I kept my rows down to around 60.000 as it should. Also, to avoid the dreaded message prompt, I added: Application.DisplayAlerts = False ..around the pieces of code Application.DisplayAlerts = True

Hope it helps someone in the future ;-)

Upvotes: 2

Amy
Amy

Reputation: 177

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

Upvotes: 1

Bond
Bond

Reputation: 16311

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.

Upvotes: 7

Sharunas Bielskis
Sharunas Bielskis

Reputation: 1203

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.

Upvotes: 2

3-14159265358979323846264
3-14159265358979323846264

Reputation: 3290

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!

Upvotes: 4

Related Questions