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
(E-Mail Removed)

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
> (E-Mail Removed)
>
>
>

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" <nigel-(E-Mail Removed)> wrote in message
news:111F76AB-EB81-4E65-BC08-(E-Mail Removed)...
> 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
> (E-Mail Removed)
>
>
>

OK thanks, got it!

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

--

Regards,
Nigel
(E-Mail Removed)



"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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" <nigel-(E-Mail Removed)> wrote in message
> news:111F76AB-EB81-4E65-BC08-(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>
>