delete modules
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 |
delete modules
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 |
All times are GMT +1. The time now is 12:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com