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 here:
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
>
>
>