Unhiding multiple worksheets based on a single cell value

[glad_ir]

Hi,

I'm hoping for some help please.

I've just started writing the code below to unhide worksheets based on a value in a single cell. The code works but I have lots of sheets to unhide - my cases go up to 27 which will unhide 27 sheets so a lot of typing!

Please could somebody advise if there is a way to unhide multiple sheets in 1 line of code e.g. could case 3 below be converted to something along the lines of Worksheets("1,2,3").Visible = True?

Thank you,
Iain

Sub macro10()

Select Case Worksheets("INSTRUCTIONS").Range("G10").Value

Case "1"
    Worksheets("1").Visible = True
Case "2"
    Worksheets("1").Visible = True
    Worksheets("2").Visible = True
Case "3"
    Worksheets("1").Visible = True
    Worksheets("2").Visible = True
    Worksheets("3").Visible = True

End Select
End Sub

 

[Fluff]

Are your sheet names 1,2,3 etc or are they something different?

 

[glad_ir]

Are your sheet names 1,2,3 etc or are they something different?

Hi Fluff, sorry I hadn't appreciated the version had changed - I'll update. Thank you for replying. The sheets names start off as 1, 2, 3 but after the macro to unhide them is run I have another macro that renames them based on user input to a range of cells. Ideally I'd like the sheets unhide macro to also be able to run after the sheets have been renamed. Is it possible to write the code to refer to the fixed sheet name (is it called component name? i.e.. Sheets(1)) rather than what the sheet has been named?

 

[My Aswer Is This]

Assuming your want to show sheets 1 to 5 or 1 to 10
And we are talking about sheet numbers not sheet names
you do not need select case.
Since it seems as if you always want to see sheets(1) to the sheet number in Range("G10") of sheet name "INSTRUCTIONS"
And you never mentioned hiding any sheets. If you want all the other sheets hidden you will have to tell us that.

Try this:

Sub Show_Some_Sheets()
'Modified  1/8/2021  9:34:49 AM  EST
Dim ans As Long
ans = Worksheets("INSTRUCTIONS").Range("G10").Value
    For i = 1 To ans
        Sheets(i).Visible = True
    Next
End Sub

 

[Fluff]

If you want the sheet index, that is it's position in the workbook, then you could use the code from @My Aswer Is This. However if a user changes the order of the sheets you could have a problem.

Another option, if the code is located in the same workbook as the sheets, would be to use the sheet code name, as seen in the project window of the VB Editor.

Sub glad_ir()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(Sheet1, Sheet2, Sheet3)
   For i = 0 To Worksheets("INSTRUCTIONS").Range("G10").Value - 1
      Ary(i).Visible = xlSheetVisible
   Next i
End Sub

 

[glad_ir]

Thanks you both very much. These both work well. The flexibility to cope to the user changing the sheet order will help the robustness of the sheet.

Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[My Aswer Is This]

I do not understand Fluff script.
I tried it and I do not understand it.

Are you saying all the sheets that you want visible have got to be part of the array?
So if user has 100 sheets he wants visible he must enter 100 sheets in the array.
That would be a lot of work.

 

[Fluff]

Are you saying all the sheets that you want visible have got to be part of the array?

Yes that's right & I agree that a lot of sheets is a pain to set up. As the OP said sheet names would change there are two basic options.
1) Do it like you did using the sheet index, which is fine until somebody starts messing with the order of the sheets.
2) Use the sheet codenames, like I did, where it doesn't matter what order the sheets are in.