(Solved) – How to duplicate a workshhet without duplicating its Worksheet Private sub code


I’m trying to duplicate a worksheet with a macro, but when doing so, to private sub is duplicated as well, what I don’t want to happen because it interferes afterward with another module macro. I have seen this post Copy a worksheet without copying the code and tried, but it doesn’t work. Some ideas on how to do that?

ActiveSheet.Copy After:=Sheets("CB")

' Get the code/object name of the new sheet...
Dim Code As String
Code = "Private sub Worksheet_Change(ByVal Target As Range)"

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

The worksheet change sub should not work on the new page, so how can I do that?

Leave a Reply

Your email address will not be published. Required fields are marked *