Delete Specific Sheets
 

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

Delete Specific Sheets
 

This might be the culprit.

If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then

Try:

If wsSheet.Range(sSheetType).Value = "Project" Or _
wsSheet.Range(sSheetType).Value = "Summary" Then


"Kevin H. Stecyk" wrote:

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

Delete Specific Sheets
 

"JLGWhiz" wrote in message

If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then

Try:

If wsSheet.Range(sSheetType).Value = "Project" Or _
wsSheet.Range(sSheetType).Value = "Summary" Then

Yes, that solved my problem. Thank you!

Best regards,
Kevin