VBA looping through sheets codenames

[telesien]

Hi, I am solving a problem.
I need a macro, that does certain operations on several sheets in workbook, but due to the actual sheet names changing based on specific projects and people adding new sheets, the only viable option is refering the the sheets by their codenames.

Can you loop through codenames in some easy way? I tried simply this

Dim i as Integer

For i = 20 to 50

.....

Next

But I couldn't figure out, how to refer to the sheets themselves. Sadly no variation of

Sheet & i

seems to work, although we are talking about sheets with codenames Sheet20...Sheet50

I either get "type mismatch" error message

Any idea?

Thanks

 

[steve the fish]

This should help:

For Each sh In ThisWorkbook.Worksheets
    mySh = Replace(sh.CodeName, "Sheet", "")
    If mySh >= 20 And mySh <= 50 Then
        MsgBox sh.Name
    End If
Next

 

[pgc01]

Hi

You first have to get the worksheet object from the codename, then you use it as usual.

This is a simple example that gets the worksheet objects from the codenames Sheet1, Sheet2 and Sheet3 and then prints a debug message.

Try this in a new workbook. Rename the first 3 worksheets, change their place and then run the code.

Sub Test()
Dim prj As Object
Dim ws As Worksheet
Dim j As Long

Set prj = ActiveWorkbook.VBProject

For j = 1 To 3
    Set ws = Worksheets(prj.VBComponents("Sheet" & j).Properties("Index").Value)
    MsgBox "Codename: Sheet" & j & ", Name: " & ws.Name
Next j

End Sub

 

[John_w]

pgc01's solution requires you to set macro security to trust access to the VBA project.

For an alternative answer which doesn't need 'trust access', this is based on excel - Fully reference a worksheet by codename - Stack Overflow:

Public Sub Loop_Sheet_CodeNames()
    Dim i As Long, ws As Worksheet
    Debug.Print "CodeName", "Name"
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set ws = GetSheetWithCodeName("Sheet" & i, ActiveWorkbook)
        If Not ws Is Nothing Then
            Debug.Print ws.CodeName, ws.Name
        End If
    Next
End Sub

Public Function GetSheetWithCodeName(ByVal worksheetCodeName As String, Optional wb As Workbook) As Worksheet
    Dim i As Long
    If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
    Set GetSheetWithCodeName = Nothing
    i = 0
    While GetSheetWithCodeName Is Nothing And i < wb.Worksheets.Count
        i = i + 1
        If wb.Worksheets(i).CodeName = worksheetCodeName Then Set GetSheetWithCodeName = wb.Worksheets(i)
    Wend
End Function

 

[telesien]

Hi

You first have to get the worksheet object from the codename, then you use it as usual.

This is a simple example that gets the worksheet objects from the codenames Sheet1, Sheet2 and Sheet3 and then prints a debug message.

Try this in a new workbook. Rename the first 3 worksheets, change their place and then run the code.

Sub Test()
Dim prj As Object
Dim ws As Worksheet
Dim j As Long

Set prj = ActiveWorkbook.VBProject

For j = 1 To 3
    Set ws = Worksheets(prj.VBComponents("Sheet" & j).Properties("Index").Value)
    MsgBox "Codename: Sheet" & j & ", Name: " & ws.Name
Next j

End Sub

This is interesting, although I am not sure how that can be incorporated to my macro.

I guess it means that in theory the start is correct, but I am not nearly good enough to see how to use it in practice...

 

[pgc01]

This is interesting, although I am not sure how that can be incorporated to my macro.

I guess it means that in theory the start is correct, but I am not nearly good enough to see how to use it in practice...

Hi

After you get the worksheet object (first statement in the loop) it's easy to perform the usual operations in a range in the worksheet.

    Set ws = Worksheets(prj.VBComponents("Sheet" & j).Properties("Index").Value)

You can use it to reference any range, for ex. to write 1 in A1 and copy B2 to C3 in that worksheet, you can use

ws.Range("A1").Value = 1
ws.Range("B2").Copy Destination:=ws.Range("C3")

Hope this helps