Copy Sheet and clear macros contained in it

[massivtre]

Hello All,

Looking for some VBA help here. I have a spreadsheet which contains a number of checklists. Each main checklist is in its own sheet. All of these sheets are hidden until the user clicks on the hyperlink on the "Home" sheet (main sheet always visible). The VBA code I use to hide the sheets is written into each sheet. When the required checklist sheet is unhidden - the user then gets the option to copy this sheet with a new name from cell B7 (all original checklists need to remain unedited). My issue - when I copy the sheet with a new name it also copies the code to hide the sheet. I don't want this code to be copied to the new sheet (so the new copied sheet is always visible) - How can I achieve this? Thanks

 

[shknbk2]

Add this before your If statement:

    With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        .DeleteLines 1, .CountOfLines
    End With

 

[massivtre]

This worked great! Thanks

Just had to remember to turn on "Trust access to the VBA project object model".