variable = worksheet (=>expected identifier)

[babozo]

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 !!

[arnelgp]

can you use Collection?
you can add Item to collection:

Dim col As New Collecton

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
col.Add W, "WB00WS0" & i
End If
Next i
Next W
End Sub

[Paul_Hossler]

For example, if my sheet's name is "BDD" when I code I will be referring 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

Why not just stay with the Codename for the worksheets in your macros. That way if doesn't matter what the user does (unless they delete a sheet)


Capture.JPG