0
votes

Using the code below where I'm able to select all sheets that contains the word "ba" on it however, if those sheets/tabs are hidden, I can't, I get a "Run-time errror '1004':" warning.

Any suggestion how could I make this code work with hidden sheet/tabs? So it shows all sheets/tabs names with "ba" even if it's hidden shows up? If they are hidden, I want them to appear or .Visible = True

Sub listray()
Dim ws As Worksheet, flg As Boolean
For Each ws In Sheets
If LCase(ws.Name) Like "*ba*" Then
    ws.Select Not flg
    flg = True
End If
Next
End Sub
End Sub
2
Why do you need to Select the sheet? - urdearboy
By select, I been unhide sheets that contain certain text in the name. - Magickarp
You don't need to Select a sheet to hide/unhide it. - urdearboy

2 Answers

3
votes

The sheets have to be visible to be selected.

If LCase(ws.Name) Like "*ba*" Then
    ws.Visible = xlSheetVisible
    ws.Select Not flg
    flg = True
End If
2
votes

You do not need to Select a worksheet to hide/unhide it. This will cycle through sheets and only leave sheets visible that have name Like "*ba*"

Sub listray()

Dim ws As Worksheet

Application.ScreenUpdating = False
    For Each ws In Sheets
        ws.Visible = LCase(ws.Name) Like "*ba*"
    Next
Application.ScreenUpdating = True

End Sub