I have a situation where I'm trying to create a loop that opens a worksheet
that has the same name as a range or cell or variable. I can capture the
name from the value in a cell, but then when I try to use that name as the
name of the worksheet I get a datatype conflict. Here's an example from the
code...

Set NextTab = Users(UserLoop) ' where Users & NextTab are defined as
ranges
Set WS1 = Worksheets(NextTab) ' can't use a range this way

Any way around this?
Thanks,
Steve

How do you open a worksheet?

Are you trying to open a workbook, or activate a worksheet in the active
workbook?

--

Vasant


"stebro" wrote in message
...
I have a situation where I'm trying to create a loop that opens a worksheet
that has the same name as a range or cell or variable. I can capture the
name from the value in a cell, but then when I try to use that name as the
name of the worksheet I get a datatype conflict. Here's an example from
the
code...

Set NextTab = Users(UserLoop) ' where Users & NextTab are defined as
ranges
Set WS1 = Worksheets(NextTab) ' can't use a range this way

Any way around this?
Thanks,
Steve

Thanks for the clarification. The loop is trying to access one worksheet and
act on values, then loop to the next worksheet based on the name it finds in
the array and act on more velues in cells, etc.

Does this help? If not please forgive me - I'm way outside my comfort zone;
this type of project comes along about once every three years. Thank you so
much for your assistance ...

Steve


"Vasant Nanavati" wrote:

How do you open a worksheet?

Are you trying to open a workbook, or activate a worksheet in the active
workbook?

--

Vasant


"stebro" wrote in message
...
I have a situation where I'm trying to create a loop that opens a worksheet
that has the same name as a range or cell or variable. I can capture the
name from the value in a cell, but then when I try to use that name as the
name of the worksheet I get a datatype conflict. Here's an example from
the
code...

Set NextTab = Users(UserLoop) ' where Users & NextTab are defined as
ranges
Set WS1 = Worksheets(NextTab) ' can't use a range this way

Any way around this?
Thanks,
Steve

Try:

WS1 = Worksheets(NextTab.Value)

Although Value is the default property of the Range object, in this case it
seems necessary to explicitly indicate that the name of the worksheet is the
value of the NextTab range.

--

Vasant



"stebro" wrote in message
...
Thanks for the clarification. The loop is trying to access one worksheet
and
act on values, then loop to the next worksheet based on the name it finds
in
the array and act on more velues in cells, etc.

Does this help? If not please forgive me - I'm way outside my comfort
zone;
this type of project comes along about once every three years. Thank you
so
much for your assistance ...

Steve


"Vasant Nanavati" wrote:

How do you open a worksheet?

Are you trying to open a workbook, or activate a worksheet in the active
workbook?

--

Vasant


"stebro" wrote in message
...
I have a situation where I'm trying to create a loop that opens a
worksheet
that has the same name as a range or cell or variable. I can capture
the
name from the value in a cell, but then when I try to use that name as
the
name of the worksheet I get a datatype conflict. Here's an example
from
the
code...

Set NextTab = Users(UserLoop) ' where Users & NextTab are defined
as
ranges
Set WS1 = Worksheets(NextTab) ' can't use a range this way

Any way around this?
Thanks,
Steve

Sorry; meant:

Set WS1 = Worksheets(NextTab.Value)

of course.

--

Vasant



"Vasant Nanavati" <vasantn AT aol DOT com wrote in message
...
Try:

WS1 = Worksheets(NextTab.Value)

Although Value is the default property of the Range object, in this case
it seems necessary to explicitly indicate that the name of the worksheet
is the value of the NextTab range.

--

Vasant



"stebro" wrote in message
...
Thanks for the clarification. The loop is trying to access one worksheet
and
act on values, then loop to the next worksheet based on the name it finds
in
the array and act on more velues in cells, etc.

Does this help? If not please forgive me - I'm way outside my comfort
zone;
this type of project comes along about once every three years. Thank
you so
much for your assistance ...

Steve


"Vasant Nanavati" wrote:

How do you open a worksheet?

Are you trying to open a workbook, or activate a worksheet in the active
workbook?

--

Vasant


"stebro" wrote in message
...
I have a situation where I'm trying to create a loop that opens a
worksheet
that has the same name as a range or cell or variable. I can capture
the
name from the value in a cell, but then when I try to use that name as
the
name of the worksheet I get a datatype conflict. Here's an example
from
the
code...

Set NextTab = Users(UserLoop) ' where Users & NextTab are defined
as
ranges
Set WS1 = Worksheets(NextTab) ' can't use a range this way

Any way around this?
Thanks,
Steve