Unhide Worksheets Between to Specific Worksheets

[lindz5970]

I'm trying to create a macro that will unhide multiple worksheets between 2 specific worksheets. For example, my first tab is called "Start" and the last tab is called "Finish" and I want the macro to unhide all of the tabs found b/t "Start" and "Finish". Any help is greatly appreciated!

Thanks!

 

[Caleeco]

Hi,

I think this should do what you want, however:
1. Your Sheets need to be named exactly "Start" and "Finish" (case-sensitive)
2. The sheet named "Start" needs to physically precede the one that is named "Finish"

Sub UnhideSheets()


Dim iWSCount As Integer
Dim i As Integer
Dim shHidden As Boolean


iWSCount = ActiveWorkbook.Worksheets.Count
shHidden = False
For i = 1 To iWSCount
    If shHidden = True Then
        Worksheets(i).Visible = True
    End If
     
    If Worksheets(i).Name = "Start" Then
        shHidden = True
    ElseIf Worksheets(i).Name = "Finish" Then
        shHidden = False
    End If
Next i


End Sub

Let me know if it works
Caleeco

 

[Sgdva]

You need to call this one from another sub, just name the arguments SheetOne and SheetTwo (macro will determine the limits for them). Workbook is optional (if not set activeworkbook will be evaluated)

Example to call it:

Sub Main()
Call UnhideSheets("Sheet1", "Sheet3")
End Sub

Real thing

Sub UnhideSheets(SheetOne As String, SheetTwo As String, Optional WorkBookEvaluated As Workbook)
Dim TotalSheets As Long
Dim CounterSheets As Long
If WorkBookEvaluated Is Nothing Then Set WorkBookEvaluated = ThisWorkbook
On Error GoTo Err01UnhideSheets
If WorkBookEvaluated.Sheets(SheetOne).Index - WorkBookEvaluated.Sheets(SheetTwo).Index = 0 Then MsgBox ("Err02UnhideSheets: Either sheet " & SheetOne & " or sheet " & SheetTwo & " don't exist! Or they are the same! "): End
TotalSheets = IIf(WorkBookEvaluated.Sheets(SheetOne).Index > WorkBookEvaluated.Sheets(SheetTwo).Index, WorkBookEvaluated.Sheets(SheetOne).Index, WorkBookEvaluated.Sheets(SheetTwo).Index)
For CounterSheets = IIf(WorkBookEvaluated.Sheets(SheetOne).Index > WorkBookEvaluated.Sheets(SheetTwo).Index, WorkBookEvaluated.Sheets(SheetTwo).Index, WorkBookEvaluated.Sheets(SheetOne).Index) To TotalSheets
WorkBookEvaluated.Sheets(CounterSheets).Visible = True
Next CounterSheets
If 1 = 2 Then
Err01UnhideSheets:
MsgBox ("Err01UnhideSheets: Either sheet " & SheetOne & " or sheet " & SheetTwo & " don't exist!"): End
End
End If
End Sub

 

[lindz5970]

Hi,

I think this should do what you want, however:
1. Your Sheets need to be named exactly "Start" and "Finish" (case-sensitive)
2. The sheet named "Start" needs to physically precede the one that is named "Finish"

Sub UnhideSheets()


Dim iWSCount As Integer
Dim i As Integer
Dim shHidden As Boolean


iWSCount = ActiveWorkbook.Worksheets.Count
shHidden = False
For i = 1 To iWSCount
    If shHidden = True Then
        Worksheets(i).Visible = True
    End If
     
    If Worksheets(i).Name = "Start" Then
        shHidden = True
    ElseIf Worksheets(i).Name = "Finish" Then
        shHidden = False
    End If
Next i


End Sub

Let me know if it works
Caleeco

Caleeco - that worked! Thank you so much for your help!

 

[Caleeco]

You're welcome Glad I could help!