Send Error Report 2

[FJAY]

I keep getting a send error report when trying to delete some worksheets using the function or macros below:

Public Sub delete_worksheet()


Dim shtDelWorkSheet As Worksheet

For Each shtDelWorkSheet In Application.Worksheets

Select Case shtDelWorkSheet.Name
Case "SAGE_VAR"
Application.DisplayAlerts = False
Sheets("SAGE_VAR").Select
Sheets("SAGE_VAR").Delete
Application.DisplayAlerts = True

Case "Network_Parameters"
Call remove_objects_NetworkParameters

Case "INTERFACE"
Call remove_objects_interface

Case "InputWindow"

Call remove_objects_InputWindow

Case "SETUP"
Application.DisplayAlerts = False
Sheets("SETUP").Select
Sheets("SETUP").Delete
Application.DisplayAlerts = True

Case "Brew_your_own Templates"
Application.DisplayAlerts = False
Sheets("Brew_your_own Templates").Select
Sheets("Brew_your_own Templates").Delete
Application.DisplayAlerts = True

Case "Brew_NAW"
Application.DisplayAlerts = False
Sheets("Brew_NAW").Select
Sheets("Brew_NAW").Delete
Application.DisplayAlerts = True

Case "Brew_Sprint_IP"
Application.DisplayAlerts = False
Sheets("Brew_Sprint_IP").Select
Sheets("Brew_Sprint_IP").Delete
Application.DisplayAlerts = True

Case Else
'do nothing
End Select
Set shtDelWorkSheet = Nothing
Next

End Sub

 

[PHV]

You may try to replace this:
For Each shtDelWorkSheet In Application.Worksheets
By this:
Dim i As Integer
For i = Application.Worksheets.Count To 1 Step -1
Set shtDelWorkSheet = Application.Worksheets(i)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

The Worksheets Collection not part of the Application Object. Rather, it is part of the Workbook Object

For Each shtDelWorkSheet In ActiveWorkbook.Worksheets

Next

BETTER CODE -- NOTICE the shtDelWorkSheet should NOT be deleted!

Public Sub delete_worksheet()
    Dim shtDelWorkSheet As Worksheet
    
    For Each shtDelWorkSheet In Application.Worksheets
        With shtDelWorkSheet
            Select Case .Name
                Case "SAGE_VAR", "SETUP", "Brew_your_own Templates", "Brew_NAW", "Brew_Sprint_IP"
                    Application.DisplayAlerts = False
                    .Delete
                    Application.DisplayAlerts = True
                    
                Case "Network_Parameters"
                    Call remove_objects_NetworkParameters
                    
                Case "INTERFACE"
                    Call remove_objects_interface
                    
                Case "InputWindow"
                    
                    Call remove_objects_InputWindow
                    
                Case Else
                    'do nothing
            End Select
        End With
    Next
    
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

oops, missed the activeworkbook thing...

Public Sub delete_worksheet()
    Dim shtDelWorkSheet As Worksheet
    
    For Each shtDelWorkSheet In ActiveWorkbook.Worksheets
        With shtDelWorkSheet
            Select Case .Name
                Case "SAGE_VAR", "SETUP", "Brew_your_own Templates", "Brew_NAW", "Brew_Sprint_IP"
                    Application.DisplayAlerts = False
                    .Delete
                    Application.DisplayAlerts = True
                Case "Network_Parameters"
                    Call remove_objects_NetworkParameters
                Case "INTERFACE"
                    Call remove_objects_interface
                Case "InputWindow"
                    Call remove_objects_InputWindow
                Case Else
                    'do nothing
            End Select
        End With
    Next
    
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[FJAY]

Implemented the feedbacks but I'm still getting the send error report. Thanks.

 

[SkipVought]

Use your debug [Step] to step thru the code and find out where the error is raised.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]