Use a range to Unhide/hide multiple sheets with a macro or VBA

[ohmedic88]

I have a Workbook that has around 400 hidden sheets. I need to do a consolidation of data from 104 of those hidden sheets.

I have a list of the different tab names in a database inside the workbook in Tab HALB List (or Sheet 1) Column C Benchmix (C5:C308).

I found a code that would unhide with an if statement (Below). However I need to be more selective. My Tab names are number based similar to this 12345678_rev_012345678910. The first 8 digits are key. How can I write a code to cover only the data I need to unhide for this task?

[COLOR=#000000]Sub Unhide_Sheets_Containing()[/COLOR]Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "pivot") > 0 Then
            ws.Visible = xlSheetVisible
        End If
    Next ws
 [COLOR=#000000]End Sub[/COLOR]

Any assistance is greatly appreciated.

Eric

 

[dmt32]

Hi,
as an idea maybe something like this:

Sub Unhide_Sheets_Containing()
    Dim ws As Worksheet
    Dim i As Integer
    Dim wsNames(1 To 2) As Variant
    Dim sh As String, Prompt As String
    
    i = 1
    Do
        Prompt = "Unhide Sheets Named " & IIf(i = 1, "From:", "To:") & Chr(10) & Chr(10) & _
        "(Enter First 8 Numbers Only)"
        wsNames(i) = InputBox(Prompt, "UnHide Sheets")
'cancel pressed
        If StrPtr(wsNames(i)) = 0 Then Exit Sub
        If wsNames(i) Like "########" Then i = i + 1
    Loop Until i > 2
    
    For Each ws In ThisWorkbook.Worksheets
        sh = Mid(ws.Name, 1, 8)
        If sh Like "########" Then
            If Val(sh) >= Val(wsNames(1)) And Val(sh) <= Val(wsNames(2)) Then ws.Visible = xlSheetVisible
        End If
    Next ws
End Sub

Code assumes sheet names (first 8 numbers) are incremental. Also, code will only Unhide sheets in number range you specify - it does not re-hide them - further code will need to be added if this is required.

Hope Helpful

Dave

 

[ohmedic88]

That works real nice however, my numbers are more random and not in order. My A column is my sorting column which is rated 1 through 5. I need to unhide 1, 2, and 3.

Any suggestions?