[RESOLVED] Delete code in workbooks using a macro

I am trying to write a macro that will delete the code behind a particular worksheet. I tried the following code but I keep getting a compile error after it deletes the lines.

Code Code:

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
 
Set VBProj = ActiveWorkbook.VBProject
Set CodeMod = ActiveWorkbook.VBProject.VBComponents("sheet1").CodeModule
 
CodeMod.DeleteLines 1, CodeMod.CountOfLines

First I get the following compile error:

"Only comments may appear after End Sub, End Function, or End Property"

I click "OK" and then I get the following run-time error:

"Method '_Default' of object 'Range' failed."

Regardless if I hit Debug or End, the program closes.

Any help is appreciated.
Chrissy

Where are you trying to sub this code from?

It sounds like you are running it from the declarations section rather than from an actual sub routine.

No, it is in a sub within a module...

vb Code:

Sub DeleteCode()
 
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Workbooks("07540.xls").Activate
Set VBProj = Workbooks("07540.xls").VBProject
Set CodeMod = ActiveWorkbook.VBProject.VBComponents("sheet1").CodeModule
 
CodeMod.DeleteLines 1, CodeMod.CountOfLines
 
End Sub

The code actually runs, it is AFTER it deletes the lines that it gives the error.

Nevermind... the code works... the problem is on another row because of the deletion. Sorry!