Set Worksheet value from text within a cell 2

[Welshbird]

Hi All,

I'm trying to set a worksheet object to be the value in a specific cell (kinda like using a global variable, but hopefully more reliable!_

So far I have

Dim rData As Range
Dim ws As Worksheet
Set rData = Range("Lookup!E33")
Set ws = Range("Lookup!E34").Value

I've truied with and without the .Value - tried using a string variable and trying to get the worksheet name from that.... all to no avail. I think I must be missing something obvious here.....

Thanks all.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[madonnac]

set was = Worksheets(Range("Lookup!E34").Value)

 

[SkipVought]

hi,

Dim ws As Worksheet
Set ws = WorkSheets(Sheets("Lookup").Range("E34").Value)

or

Dim ws As Worksheet, sName as String
sName = Sheets("Lookup").Range("E34").Value
Set ws = Worksheets(sName)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Welshbird]

I love you guys!



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Hmm. That seems to be fine on Friday night..... Not so much this morning.

I know have:

Dim ws As Worksheets
Set ws = Worksheets(Range("Lookup!E34").Value)
ws.Select

but I get runtime error 13 type mismatch.
I can see in debug the value of the range, but ws = nothing....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

In fact, even this doens't work:
set ws = Worksheets("OptionOne")

I get a type mismatch....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Dim ws as worksheet
NOT dim ws as worksheetS

Twit...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen