Unhide sheets names using VBA whose name contain specific word

Question

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

By select, I been unhide sheets that contain certain text in the name. — Magickarp
– Magickarp, Commented Sep 20, 2018 at 21:06

Valon Miller · Accepted Answer · 2018-09-20 20:38:39Z

3

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

answered Sep 20, 2018 at 20:38

Valon Miller

1,1566 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

urdearboy · Accepted Answer · 2018-09-20 21:09:19Z

2

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

edited Sep 20, 2018 at 21:09

answered Sep 20, 2018 at 20:40

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

2 Comments

Valon Miller Over a year ago

This is also valid if all that needs to be done is the unhiding. It's unclear if there are followup actions after the sheets are selected though and the OP's reply to your question didn't really make it any less of an unknown. I'm giving this answer an upvote based on the comment reply that said the select was just to perform the unhide.

urdearboy Over a year ago

Yea that's what i'm basing this answer on. If it doesn't fit OPs need, and the Boolean flag is used for other things, than maybe this will do for future readers :p

Collectives™ on Stack Overflow

Unhide sheets names using VBA whose name contain specific word

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related