FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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
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
VBA Code:
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
VBA Code:
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