Hi All
I have the codenames for various worksheets stored as strings in another
worksheet. How can I assign each sheet to an object?

e.g. If I have the string "myCodeName" which is the codename for one of the
worksheets, and want to assign it to a worksheet object

Dim wSh as Worksheet
Set wSh = ??

TIA
--

Regards,
Nigel

Set wSh = sheets("myCodeName")


"Nigel" wrote:

Hi All
I have the codenames for various worksheets stored as strings in another
worksheet. How can I assign each sheet to an object?

e.g. If I have the string "myCodeName" which is the codename for one of the
worksheets, and want to assign it to a worksheet object

Dim wSh as Worksheet
Set wSh = ??

TIA
--

Regards,
Nigel

Ah, your other post makes more sense now. You need to loop sheets to find
it.

Try the following, rename your sheet that has the codename Sheet2

Sub test()
Dim res As Long
Dim s$
Dim ws As Worksheet
s = "Sheet2" ' the codename

res = WsFromCodeName(s, ActiveWorkbook, ws)
If res = 1 Then
MsgBox ws.Name
ElseIf res = 2 Then
MsgBox "can't return all codenames"
Else
MsgBox s & " not found"
End If

End Sub

Function WsFromCodeName(ByVal sCodeName As String, _
ByVal wb As Workbook, _
ByRef ws As Worksheet) As Long
Dim s As String
For Each ws In wb.Worksheets
s = ws.CodeName
If s = "" Then
' sheet inserted since last saved
WsFromCodeName = 2
ElseIf s = sCodeName Then
WsFromCodeName = 1
Exit For
End If
Next

End Function

Regards,
Peter T

"Nigel" wrote in message
...
Hi All
I have the codenames for various worksheets stored as strings in another
worksheet. How can I assign each sheet to an object?

e.g. If I have the string "myCodeName" which is the codename for one of
the
worksheets, and want to assign it to a worksheet object

Dim wSh as Worksheet
Set wSh = ??

TIA
--

Regards,
Nigel

OK thanks, got it!

I need to scan all sheets to find the codename which IDs the relevant sheet

--

Regards,
Nigel




"Peter T" <peter_t@discussions wrote in message
...
Ah, your other post makes more sense now. You need to loop sheets to find
it.

Try the following, rename your sheet that has the codename Sheet2

Sub test()
Dim res As Long
Dim s$
Dim ws As Worksheet
s = "Sheet2" ' the codename

res = WsFromCodeName(s, ActiveWorkbook, ws)
If res = 1 Then
MsgBox ws.Name
ElseIf res = 2 Then
MsgBox "can't return all codenames"
Else
MsgBox s & " not found"
End If

End Sub

Function WsFromCodeName(ByVal sCodeName As String, _
ByVal wb As Workbook, _
ByRef ws As Worksheet) As Long
Dim s As String
For Each ws In wb.Worksheets
s = ws.CodeName
If s = "" Then
' sheet inserted since last saved
WsFromCodeName = 2
ElseIf s = sCodeName Then
WsFromCodeName = 1
Exit For
End If
Next

End Function

Regards,
Peter T

"Nigel" wrote in message
...
Hi All
I have the codenames for various worksheets stored as strings in another
worksheet. How can I assign each sheet to an object?

e.g. If I have the string "myCodeName" which is the codename for one of
the
worksheets, and want to assign it to a worksheet object

Dim wSh as Worksheet
Set wSh = ??

TIA
--

Regards,
Nigel