I think this is getting somewhere, but not quite.

What I have now is:

Dim ws As Worksheet
Dim ss As Range
Set ws = Sheets("SheetNames")
Set ss = Sheets("SheetNames").Range("D2")
then some other code, and eventually
ws.activate
ss.select

It works to the point of activating the sheet called "SheetNames" by the
command ws.activate, but it totally ignores the command ss.select.
When I hover my mouse over the ss variable before the procedure ends, it
shows "ss = sheet6" which is what I expect, but it doesn't activate that
sheet.
The formula in the sheets called "SheetNames" in cell D2 is
="Sheet"&VLOOKUP(TRUE,A2:D60,4,FALSE) and the result is Sheet6
If I change that to simply VLOOKUP(TRUE,A2:D60,4,FALSE) the result is 6.
But that doesn't work either.

Rob



"FSt1" wrote in message
...
hi
you're trying to select too much at once.
to select a range, the sheet that has the range must be selected first.
dim ws as worksheet
dim ss as range
set ws = sheets("sheet60")
set ss = sheets("sheet60").range("D2")
ws.activate
ss.select

regards
FSt1
"RobN" wrote:

(I posted this in the office.misc newsgroup by mistake)

I've used the following to try and have VB select a sheet dependant on
the
value in d2.
Dim ss As Variant
Set ss = Sheet60.Range("d2")

How do I apply that now to select that sheet? I thought something like
sheet(ss).select would do it, but it doesn't work

Sheets(ss).select does work, but doesn't go to the correct sheet number,
whereas Sheet(ss) brings up a "Sub or Function not defined" error.

Rob