Reference Worksheet Codename in a loop

[FryGirl]

I found this reference a believe it's what I need, but not sure how to pass the codename to the function to get the object.

Reference Worksheet Codename using a string variable? - OzGrid Free Excel/VBA Help Forum

Right so in a nutshell I've got a load of active X command buttons which are used to: 1. Copy a VBA Generated SQL from a Cell (each with a named range with a common prefix followed by a number) 2. Activate the sheet which the user should paste the…

www.ozgrid.com

I've created three sheets with codename like, ws1, ws2, ws3. I would like to run a loop to those sheets using the first two letters (ws) combined with the number.

I've hit a wall with this

Sub addvalue()
Dim mySheet As String
Dim i As Long
For i = 1 To 3
mySheet = "ws" & i
SheetFromCodeName (mySheet)
mySheet.Range("A1").Value = "Yes"
Next i
End Sub

Function SheetFromCodeName(aName As String, Optional wb As Workbook) As Worksheet
'https://www.ozgrid.com/forum/index.php?thread/132768-reference-worksheet-codename-using-a-string-variable/
If wb Is Nothing Then Set wb = ThisWorkbook
With wb
Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index"))
End With
End Function

 

[CSmith]

Why not just use

Sheets(mySheet).Range("A1").Value

?

I found this reference a believe it's what I need, but not sure how to pass the codename to the function to get the object.

Reference Worksheet Codename using a string variable? - OzGrid Free Excel/VBA Help Forum

Right so in a nutshell I've got a load of active X command buttons which are used to: 1. Copy a VBA Generated SQL from a Cell (each with a named range with a common prefix followed by a number) 2. Activate the sheet which the user should paste the…

www.ozgrid.com

I've created three sheets with codename like, ws1, ws2, ws3. I would like to run a loop to those sheets using the first two letters (ws) combined with the number.

I've hit a wall with this

Sub addvalue()
Dim mySheet As String
Dim i As Long
For i = 1 To 3
mySheet = "ws" & i
SheetFromCodeName (mySheet)
mySheet.Range("A1").Value = "Yes"
Next i
End Sub

Function SheetFromCodeName(aName As String, Optional wb As Workbook) As Worksheet
'https://www.ozgrid.com/forum/index.php?thread/132768-reference-worksheet-codename-using-a-string-variable/
If wb Is Nothing Then Set wb = ThisWorkbook
With wb
Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index"))
End With
End Function

 

[FryGirl]

Wouldn't that get me the Sheet name when I'm looking for the codename. I tried that and I get "subscript out of range"

Sub addvalue()
    Dim mySheet As String
    Dim i As Long
    For i = 1 To 3
        mySheet = "ws" & i
        Sheets(mySheet).Range("A1").Value = "Yes"
    Next i
End Sub

 

[CSmith]

Do you need to use their code name? If so, then try with your earlier function.

Sub addvalue()
  Dim wSheet As String, mySheet As Variant
  Dim i As Long
  For i = 1 To 3
    wSheet = "ws" & i
    Set mySheet = SheetFromCodeName(wSheet)
    Sheets(mySheet).Range("A1").Value = "Yes"
  Next i
  Set mySheet = Nothing
End Sub

Also you will need to make sure the Macro Settings // Developer Macro Settings // Trust access to the VBA project object model CHECKBOX has been checked to use your code above...

 

[FryGirl]

Thank you CSmith. One change and it worked great.

Instead of

Sheets(mySheet).Range("A1").Value = "Yes"

Change to

mySheet.Range("A1").Value = "Yes"

 

[CSmith]

Opps ya that was copy paste error on my part... I used Debug.Print line... Glad to help Thanks for the update! My test code

Sub addvalue()
  Dim wSheet As String, mySheet As Variant
  Dim i As Long
  For i = 1 To 3
    wSheet = "Sheet" & i
    Set mySheet = SheetFromCodeName(wSheet)
    Debug.Print mySheet.Name
  Next i
  Set mySheet = Nothing
End Sub