Macro to hide sheets with specific names

[floridagunner]

Hello,

I need a macro that will hide sheet tabs with certain names as well as delete other sheets. However when it comes to processing the code where it has to check for sheet names and hide or leave alone, I am running into an error.

Below is the code I'm using:


Sub DeleteSheets2()

''Delete all worksheets except sheet 1

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Variance Evaluation" And ws.Name <> "Analysis" And ws.Name <> "Device Removal Pivot Table" Then ws.Delete
Next
Application.DisplayAlerts = True
If ws.Name = "Variance Evaluation" Then ws.Visible = False
If ws.Name = "Analysis" Then ws.Visible = False
If ws.Name = "Device Removal Pivot Table" Then ws.Visible = False
''Add worksheets

Sheets("Sheet1").Select
Sheets.Add
ActiveSheet.Name = "Sheet3"
Sheets.Add
ActiveSheet.Name = "Sheet2"
Sheets("Sheet1").Select
Sheets("Sheet1").Move Before:=Sheets(1)

End Sub

Can anyone tell me what I'm doing wrong?

Thanks

 

[VoG]

What error on which line of code? Is the workbook structure protected?

 

[floridagunner]

Hello VOG,

The workbook structure is not protected and the error occurs on the following line of code:

If ws.Name = "Variance Evaluation" Then ws.Visible = False

When I run the macro it processes the code above that line. Then the error message comes up with the following message:

"Run-time error '91':
Object Variable or With block variable not set"

This is followed by the option to press the "Debug" button.

I should also add that in this particular case the file does not have the sheets named "Variance Evaluation" or "Analysis". However I want the macro to check if sheets by that name exist, and if they do then the macro should hide them.

Thanks

 

[VoG]

Try this

Sub DeleteSheets2()

''Delete all worksheets except sheet 1

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name = "Variance Evaluation" Then ws.Visible = False
    If ws.Name = "Analysis" Then ws.Visible = False
    If ws.Name = "Device Removal Pivot Table" Then ws.Visible = False
    If ws.Name <> "Sheet1" And ws.Name <> "Variance Evaluation" And ws.Name <> "Analysis" And ws.Name <> "Device Removal Pivot Table" Then ws.Delete
Next
Application.DisplayAlerts = True
''Add worksheets

Sheets("Sheet1").Select
Sheets.Add
ActiveSheet.Name = "Sheet3"
Sheets.Add
ActiveSheet.Name = "Sheet2"
Sheets("Sheet1").Select
Sheets("Sheet1").Move Before:=Sheets(1)

End Sub

 

[floridagunner]

It worked. Thanks VoG!!