[RESOLVED] Delete code in workbooks using a macro

[Chrissy]

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.

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

[Hack]

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.

[Chrissy]

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

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

[Chrissy]

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

[Chrissy]

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