i want to remove all modules of code and delete all sheets excep those
starting with the word "FULL"
here is my attempt but, the residual file is always corrupt, and connot be
opened. Can someone please tell me where im am going wrong. (Excel 2000)

Sunil

Sub removecode()
Dim sh As Worksheet
Dim vbCom As Object
For Each sh In ActiveWorkbook.Sheets
If Left$(sh.NAME, 5) = "FULL " Then
sh.Visible = xlSheetVisible: GoTo NxSht:
Else
sh.Delete
End If
NxSht:
Next sh

Application.WindowState = xlMinimized
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error Resume Next
For X% = 10 To 2 Step -1 ' **this code is in module1
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.ITEM("Module" & X%)
Next X%

ActiveWorkbook.Save
Application.Quit
End Sub

I think you will find your answer he
http://www.cpearson.com/excel/vbe.aspx

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"sunilpatel" wrote:

i want to remove all modules of code and delete all sheets excep those
starting with the word "FULL"
here is my attempt but, the residual file is always corrupt, and connot be
opened. Can someone please tell me where im am going wrong. (Excel 2000)

Sunil

Sub removecode()
Dim sh As Worksheet
Dim vbCom As Object
For Each sh In ActiveWorkbook.Sheets
If Left$(sh.NAME, 5) = "FULL " Then
sh.Visible = xlSheetVisible: GoTo NxSht:
Else
sh.Delete
End If
NxSht:
Next sh

Application.WindowState = xlMinimized
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error Resume Next
For X% = 10 To 2 Step -1 ' **this code is in module1
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
vbCom.Remove VBComponent:=vbCom.ITEM("Module" & X%)
Next X%

ActiveWorkbook.Save
Application.Quit
End Sub