Geek Answers Handbook

Copy worksheet without copying code

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

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

However, it also copies any macros or event handlers associated with this sheet. How to copy a sheet without copying Visual Basic code?

+5
source share
4 answers

After copying the sheet, you can refer to it by name, and then delete all 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

Edit

As @MackM noted in the comments, to work with project components, you need to make sure that the option "Trust access to the VBA project object model" enabled in the Excel macro settings.

+3
source

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

 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
+4
source

Not quite what OP wanted, but you can also delete the sheet macros by copying the sheet into a new book and then saving the book using the following code (which ultimately saves the book in .xlsx format and deletes the code) ..

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

This gives the advantage of saving all data and formatting (for example, column widths) and does not require VBA object model permissions.

Of course, after saving, you can open the saved book again and move the sheet back to the original book, now without code!

+2
source

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

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

but the program showed a warning, and in this case, the user must manually respond to this warning, so I added additional lines of code:

  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, the code block worked without warning, and after the formation of the workbook with the copied sheets in which the code was originally, it was closed and then opened again from its directory, the code is not in the copied sheets. In this case, the warning - Programmatic access to the Visual Basic Project is not trusted - is not issued, as it may be on some user PCs, as in the case when the code

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

was used. Thus, the advantage of this method is that you do not need to worry about the security settings on the user PC and about correctly copying data from the source sheet to an additionally created blank sheet.

+1
source

More articles:


All Articles