Using worksheet codenames to hide multiple worksheets.

[stuandrews1618]

Hello All,

First time poster but I've been following this excellent forum for a while.

I have a Workbook that on the front sheet has a Data Validation list (1 through to 32) to hide or unhide other sheets. The other sheets are called 'Item1' throught to 'Item32'

The code below is what I use:

flcell = Range("$C$9").Value

Dim flopen As Long
Dim flclose As Long

If flcell > 0 Then
    
        For flclose = 1 To 32
            Sheets("Item" & flclose).Visible = xlSheetHidden
        Next flclose
        
        For flopen = 1 To flcell
            Sheets("Item" & flopen).Visible = xlSheetVisible
        Next flopen
    
    Else
    
        For flclose = 1 To 32
            Sheets("Item" & flclose).Visible = xlSheetHidden
        Next flclose
    
    End If

This works fine as long as the sheets are called Item1 - Item32. I wanted to add a piece of code that would change the name of the sheet to the input of a certain cell on that sheet, and I have a couple of lines of code that does that. The problem is if I do this then the hide/unhide code obviously stops functioning correctly.

I looked in using the sheet codename and renamed Item1-Item32 in the VBA editor sheet properties box to FLI1 - FLI32. I thought I could exchange the Item nametag in the code to FLI as shown below but I can't get it to work. Can this be done? It's not a big deal, but I like the Idea of auto hiding tabs not needed and auto renaming ones I do.

Thanks for reading

flcell = Range("$C$9").Value

Dim flopen As Long
Dim flclose As Long

If flcell > 0 Then
    
        For flclose = 1 To 32
            Sheets(FLI & flclose).Visible = xlSheetHidden
        Next flclose
        
        For flopen = 1 To flcell
            Sheets(FLI & flopen).Visible = xlSheetVisible
        Next flopen
    
    Else
    
        For flclose = 1 To 32
            Sheets(FLI & flclose).Visible = xlSheetHidden
        Next flclose
    
    End If

[Norie]

I'm afraid you can't refer to sheets by their codename in the same way as you can their tab name.

What you could do is loop through all sheets and check the codename.

Perhaps something like this.

flcell = Range("$C$9").Value

For Each ws In ThisWorkbook.Worksheets
    If ws.CodeName Like "FLI*" Then
       If Val(Mid(ws.CodeName, 4)) <= flcell Then
           ws.Visible = xlSheetVisible
       Else
           ws.Visible = xlSheetHidden
       End If
    End If
Next ws

[stuandrews1618]

Norie,

Thank you so much, this is exactly what I was looking for. I really appreciate the speed of your response and your efforts to help me.