variable = worksheet (=>expected identifier)
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:
Code:
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
Code:
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 !!
