First of all, sorry for my bad english


So I have a macro that sets the worksheets of my workbook as variables names to prevent having debugging alert when an user uses the workbook and changes the name or the position of a sheet.


For example, if my sheet's name is "BDD" when I code I will be refering to this sheet as: Sheets("'BDD").xxxxxx or sheets(1).xxxxxxx then if a user changes the name of the sheet my macros will not work anymore


That's why in worbook_open I set:


WB00 = activeworkbook
WB00WS01 = first sheet
WB00WS02 = second sheet


on and on


Therefore, when i code i refer to it as WB00WS01.xxxx


So, my macro is:




    Sub initWB00()
        Set WB00 = ActiveWorkbook
        For Each W In WB00.Worksheets
            On Error Resume Next
            If W.CodeName = "Sheet1" Then
                Set WB00WS01 = W
            End If
            If W.CodeName = "Feuil2" Then
                Set WB00WS02 = W
            End If
            If W.CodeName = "Feuil3" Then
                Set WB00WS03 = W
            End If
            If W.CodeName = "Feuil4" Then
                Set WB00WS04 = W
            End If
            If W.CodeName = "Feuil5" Then
                Set WB00WS05 = W
            End If
            If W.CodeName = "Feuil6" Then
                Set WB00WS06 = W
            End If
            If W.CodeName = "Feuil7" Then
                Set WB00WS07 = W
            End If
            If W.CodeName = "Feuil8" Then
                Set WB00WS08 = W
            End If
            If W.CodeName = "Feuil9" Then
                Set WB00WS09 = W
            End If
            If W.CodeName = "Feuil10" Then
                Set WB00WS10 = W
            End If
        Next W
    End Sub
It works as it is, but as you can see, it's pretty limited (if I want to go up to WB00WS100 for example) and it's very trivial


Then, I had the idea to loop a variable "i" that will give the number of the sheet i.e "WB00WS" & i = "sheet" & i 




Sub initWB00()
Dim i As Integer
    Set WB00 = ActiveWorkbook
    For Each W In WB00.Worksheets
        For i = 1 To 20
        If W.CodeName = "Feuil" & i Then
            Set "WB00WS0"&i = W
        End If
        Next i
    Next W
End Sub
If I do it like that I have the expected identifier error


I assume it's because I've put a string and a variable to the left of an equality but otherwise I can't imagine how can I write a loop that makes what I need to


Do you have any idea ?


Thanks in advance !!