On Sep 4, 4:08*pm, Keith wrote:
I have been spending many days trying to figure out what part of the
code is not working correctly. *I am using Excel 2003. *I followed the
instructions on the Ozgrid website (listed below).http://www.ozgrid.com/VBA/delete-sheet-code.htm

Sub DeleteSheetEventCode()
''Needs Reference Set To _
* * "Microsoft Visual Basic For Applications Extensibility"
'ToolsReferences.

Dim sSheet As Object, strName As String
* * * * For Each sSheet In Sheets
* * * * * * Select Case UCase(sSheet.Name)
* * * * * * * * Case "SHEET1", "SHEET2", "SHEET3"
* * * * * * * * * * strName = sSheet.CodeName
* * * * * * * * * * With ThisWorkbook.VBProject.VBComponents
(strName).CodeModule
* * * * * * * * * * * * * * .DeleteLines 1, .CountOfLines
* * * * * * * * * * End With
* * * * * * * * Case Else
* * * * * * * * * *'Whatever
* * * * * * End Select
* * * * Next sSheet
End Sub

I have checked the checkbox for ToolsReferenceMicrosoft Visual
Basic for Applications Extensibility 5.3 in the VBA Project.
I also have checked the checkbox for
ToolsMacroSecurity...Trusted PublishersTrust Access to Visual
Basic Project.

I run this macro and it works. *I try and run it in break mode and it
will go through two loops and then provide an error on the third. *I
call the macro, and it freezes inside of this macro (it never
completes).

I have tried this as a separate macro and called it, and also as part
of the regular macro. *I need to run code like this as my originating
worksheet is running the Worksheet_Change event macro and is causing
errors when this code is copied.

I am listing below the pertinent snippet of my code.

================================================== ===============================
* * Windows(DSCName).Activate *

* * shts = Application.Sheets.count * *
* * count = 2 * * * * * * * * * * * * * * *

* * For Each Worksheet In Worksheets * * *

* * * * Sheets(count).Activate
* * * * ActiveSheet.Copy After:=Workbooks(DWORName).Sheets(count)

* * * * Windows(DWORName).Activate * * * * * * * * * * *

'================================================= ======================================
* * * * With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule
* * * * * * * * .DeleteLines 1, .CountOfLines
* * * * End With
'================================================= ========================================

* * * * Windows(DSCName).Activate *
* * * * ActiveSheet.Range("Comments").Copy
* * * * Windows(DWORName).Activate *
* * * * ActiveSheet.Paste Destination:=Range("Comments")
* * * * SendKeys ("^{HOME}")

* * * * Windows(DSCName).Activate
* * * * FirstDay = ActiveSheet.Range("WkDateMon").Value
* * * * Windows(DWORName).Activate
* * * * With ActiveSheet
* * * * * * .Unprotect
* * * * * * .Range("WkDateMon").Value = FirstDay
* * * * End With

* * * * SendKeys ("^{HOME}") * * * * * * * * * * * * * *' Ctrl+Home
* * * * Windows(DSCName).Activate

* * * * If count = shts Then
* * * * * * Workbooks(DSCName).Close SaveChanges:=False
* * * * * * Windows(DWORName).Activate
* * * * End If
* * * * count = count + 1
* * Next

Oooops. This submitted before I could clean it up. I hope it is
easier to read here.