excel - How to clear the VBA code of a worksheet via a macro?

Question

I have a file where there's a template sheet that needs to run some code when it's activated. This sheet is being duplicated to create sheets that don't need to run this code. Currently, I have the code to check for worksheet's codename when run so that it does nothing on extra sheets, but it still slows usage down when you switch between sheets.

Is there any way to make the macro that makes duplicates also clear their VBA code contents?

(Edit) Please note that the code I need to clear is not in a module. After some research, it seems I found a way to remove modules (by accessing VBProject.VBComponents), but I'm not sure how to access the VBA code of a worksheet.

Answer 1

To remove complete code in all Sheet modules you could try something like this:

Sub Remove_some_vba_code()

Dim activeIDE As Object 'VBProject
Set activeIDE = ActiveWorkbook.VBProject

Dim Element As VBComponent

Dim LineCount As Integer
For Each Element In activeIDE.VBComponents
    If Left(Element.Name, 5) = "Sheet" Then    'change name if necessary
        LineCount = Element.CodeModule.CountOfLines

        Element.CodeModule.DeleteLines 1, LineCount
    End If
Next

End Sub