Referencing worksheet name using code name

[Salvo]

I would like to be able to reference several worksheets (the number and code name determined at run time) using their code name such as sheet5. How do I do this with code to account for the fact that this code name needs to be a variable. Thus I need to hide various sheets but their sheet name and sheet position is variable; the only thing that I know for sure is their code name. Thus I am after something like this.

for each sheet
(sheet5 as a variable).visible=false
next sheet


How do I achieve this?

[RolfJ]

Hi Salvo:

I am not quite sure whether I follow you, but may be this code example will point you in the right direction:

[vba]
Dim sh() As Worksheet
ReDim sh(0)
Set sh(0) = Sheets("Sheet1")
ReDim Preserve sh(1)
Set sh(1) = Sheets("Sheet2")
Dim s As Variant
For Each s In sh
Debug.Print s.Name
Next s

[/vba]

[Salvo]

To clarify further i have 2 columns in part of my spreadsheet such as

12 1
6 0
5 0
7 1
5 0


I then look at first column which refers to the sheet code name and the second column refers to whether sheet is visible or not.
so first row means make sheet12 visible, second row mean make sheet6 not visible and so on. I have numerous sheets in workbook and i can not use index names or sheet names as order of sheets may alter and sheets may get renamed.

[RolfJ]

I am still a bit confused: exactly which worksheets are you referring to with 'Sheet12' or 'Sheet6' in your example?

[Salvo]

Rolf J - To clarify further sheet12 = "calculation 4" , sheet6 = "calculation 7". They are just 2 of many worksheets.

Rolf J - In reference to your first reply - i could use your proposal but with 30 or so sheets I need to have 30 or so lines of code (1 for each sheet). What i am asking is there a way to automate this so that i have less than 10 lines of code regardless of whether i am dealing with 30 sheets or 100 sheets?

[geekgirlau]

Let's go back a step.

"Sheet6" is a really bad name - it's always a good idea to change the sheet name in the VBE window so that it actually means something.

Now you are referring to setting this at runtime based on 2 columns in a particular sheet. What determines whether the sheet is visible? Is this a calculation or a hard-coded value?

[RolfJ]

Hi Salvo:

Now you are really about to lose me! It would help if you were to try to explain how you index your worksheets since you insist on not using the index your workbook uses intrinsically.

[Jan Karel Pieterse]

You might use a function like this:

Public Function GetWorksheetFromCodeName(sShtCodeName) As Worksheet
'-------------------------------------------------------------------------
' Procedure : GetWorksheetFromCodeName
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 15-10-2009
' Purpose   : Returns the sheet object belonging to the codename passed.
'-------------------------------------------------------------------------
    Dim oSh As Object
    On Error Resume Next
    For Each oSh In ThisWorkbook.Worksheets
        If oSh.CodeName = sShtCodeName Then
            Set GetWorksheetFromCodeName = oSh
            Exit Function
        End If
    Next
End Function

Use the function as follows:

Dim oSht as Worksheet
Dim sName As String
sName="Sheet" & Range("A2")
Set oSht=GetWorksheetFromCodename(sName)