Hi,
I want to delete specific sheets within a workbook. I want to delete those
sheets which have a local range name (xsSheetType) set to either "Project"
or "Summary". Not all sheets will have the range name. And many of those
that do have the range name will have a value other than "Project" or
"Summary".
I tried my routine below. I know there is something wrong with this line:
If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then
When I hover over the left hand side, I will see
wsSheet.Range(sSheetType).Value = "Project"
yet it hits the error handler. This puzzles me.
I think I am very close, but missing something simple.
If there is a better method of writing this subroutine, I'd like to know.
But I am also very curious what I did wrong.
Any help is appreciated.
Regards,
Kevin
Sub DeleteOldSheets()
Dim wsSheet As Worksheet
'\ Names of sheets to be deleted
Dim sSheetName() As String
Dim iCounter1 As Integer
Dim iCounter2 As Integer
dim sSheetType as String
sSheetType = "xsSheetType"
iCounter1 = 0
ReDim sSheetName(1 To Worksheets.Count)
For Each wsSheet In Worksheets
'\ In case sheet doesn't have the specified range
On Error GoTo Err1
If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then
iCounter1 = iCounter1 + 1
sSheetName(iCounter1) = wsSheet.Name
LabelResume1:
End If
Next wsSheet
If iCounter1 = 0 Then Exit Sub
ReDim Preserve sSheetName(1 To iCounter1)
Application.DisplayAlerts = False
For iCounter2 = 1 To iCounter1
Worksheets(sSheetName(iCounter2)).Delete
Next iCounter2
Application.DisplayAlerts = True
Err1:
On Error GoTo 0
Resume LabelResume1
End Sub
|
Similar Threads
