Reference sheet Codename through Variable

[mEng]

Hi,
I've been trying to figure this one out for a while now. Currently, my VBA sub has loops that read ranges and these ranges (sheets specifically) are referenced using a the sheet indexes (ex: "sheet(1)") that are looped (ex: "For intIndex = 1 To 10"). What I really want to do is loop through the sheets using the sheet codename so that if the user or I change the tab names or index number (position of tabs), it won't break the code.

This is what I am attempting but of course the method is incorrect:

Dim SheetNumber As Integer
Dim SheetCodename As String

SheetCodename = "Sheet" & SheetNumber

For SheetNumber = 1 To 10
.
.
If Workbooks(strWorkbook).SheetCodename.Range(strRColumnArray(intCurRColumnArray) & intRRow) = "X" Then
.
.

I've seen others do something like what I am looking for using
"ThisWorkbook.VBProject.VBComponents("Sheet2").Name= "NewCodeName""
but I've heard that this causes errors for some and we use XP here so it may be problematic. I've also seen people setting the current sheet using a do loop and exiting the do loop when the current sheet matches what the string variable wants but this doesn't seem to be an option (I may be able to make it work if I rewrite the structure of how the code works but I would rather use the methods that are currently working).

Thanks in advance for any help you can offer.

 

[Jonmo1]

I don't think you can loop through the codenames..

But you can loop through all sheets and test the codename against a list of codenams you want to execute code on...

Something like

For Each ws In Sheets
    Select Case ws.CodeName
        Case "CodeName1", "Codename2", "CodeName3" 'List of all codenames you want
           'code here to run on sheets listed above
           If ws.Range(strRColumnArray(intCurRColumnArray) & intRRow) = "X" Then
       Case Else
            'Do Nothing
    End Select
Next ws

Hope that helps.

 

[mEng]

Thanks for the code. Unfortunately the program has to loop through each sheet in order to get values from them and set a destination set of cells a certain value and keep track of which sheet is active in order to copy the correct value from the correct column.
Therefore, a select wouldn't work too well. Hopefully someone on here has done what I'm trying to do.

In any case, I might just use the sheet index and try to remember to change the index values in my code if I change the order around (and lock the ability to change the order around for users).

Thanks.