Worksheets() Object
 

I'm trying to pass a text variable to the Worksheets() object where Temp is a
text variable, however, I must not be using the right syntax. I've used
Worksheets("Temp") and Worksheets(Temp) without success. What is the correct
syntax for this?
--
thanks, mc

Worksheets() Object
 

worksheets(Temp)
if Temp is a variable that holds a string.

But maybe you should test to see if that string actually contains a worksheet
name:

dim wks as worksheet
dim Temp as string

temp = "somestring"

set wks = nothing
on error resume next
set wks = worksheets(Temp)
on error goto 0

if wks is nothing then
'no worksheet with that name
else
'yes there is
end if



dallin wrote:

I'm trying to pass a text variable to the Worksheets() object where Temp is a
text variable, however, I must not be using the right syntax. I've used
Worksheets("Temp") and Worksheets(Temp) without success. What is the correct
syntax for this?
--
thanks, mc

--

Dave Peterson

Worksheets() Object
 

Thanks - I'm still receiving a subscript out of range error -- any ideas?

Dim Temp As String
Dim Wks As Worksheet
Wks = Nothing
Temp = ActiveSheet.Range("B1").Value
Wks = Worksheets(Temp)


"Dave Peterson" wrote:

worksheets(Temp)
if Temp is a variable that holds a string.

But maybe you should test to see if that string actually contains a worksheet
name:

dim wks as worksheet
dim Temp as string

temp = "somestring"

set wks = nothing
on error resume next
set wks = worksheets(Temp)
on error goto 0

if wks is nothing then
'no worksheet with that name
else
'yes there is
end if



dallin wrote:

I'm trying to pass a text variable to the Worksheets() object where Temp is a
text variable, however, I must not be using the right syntax. I've used
Worksheets("Temp") and Worksheets(Temp) without success. What is the correct
syntax for this?
--
thanks, mc

--

Dave Peterson

Worksheets() Object
 

The active workbook definitely has a sheet named with the value in B1 ?
Your error suggests there's a mis-match.

Tim


"dallin" wrote in message ...
Thanks - I'm still receiving a subscript out of range error -- any ideas?

Dim Temp As String
Dim Wks As Worksheet
Wks = Nothing
Temp = ActiveSheet.Range("B1").Value
Wks = Worksheets(Temp)


"Dave Peterson" wrote:

worksheets(Temp)
if Temp is a variable that holds a string.

Worksheets() Object
 

First, since wks represents an object (worksheet in this case), you'll want to
use:

set wks = nothing
and
set wks = worksheets(temp)

What do you have in B1?

And why did you lose the "on error" stuff?



dallin wrote:

Thanks - I'm still receiving a subscript out of range error -- any ideas?

Dim Temp As String
Dim Wks As Worksheet
Wks = Nothing
Temp = ActiveSheet.Range("B1").Value
Wks = Worksheets(Temp)

"Dave Peterson" wrote:

worksheets(Temp)
if Temp is a variable that holds a string.

But maybe you should test to see if that string actually contains a worksheet
name:

dim wks as worksheet
dim Temp as string

temp = "somestring"

set wks = nothing
on error resume next
set wks = worksheets(Temp)
on error goto 0

if wks is nothing then
'no worksheet with that name
else
'yes there is
end if



dallin wrote:

I'm trying to pass a text variable to the Worksheets() object where Temp is a
text variable, however, I must not be using the right syntax. I've used
Worksheets("Temp") and Worksheets(Temp) without success. What is the correct
syntax for this?
--
thanks, mc

--

Dave Peterson


--

Dave Peterson

Worksheets() Object
 

Thank you everyone for your help. It works!!

"Dave Peterson" wrote:

First, since wks represents an object (worksheet in this case), you'll want to
use:

set wks = nothing
and
set wks = worksheets(temp)

What do you have in B1?

And why did you lose the "on error" stuff?



dallin wrote:

Thanks - I'm still receiving a subscript out of range error -- any ideas?

Dim Temp As String
Dim Wks As Worksheet
Wks = Nothing
Temp = ActiveSheet.Range("B1").Value
Wks = Worksheets(Temp)

"Dave Peterson" wrote:

worksheets(Temp)
if Temp is a variable that holds a string.

But maybe you should test to see if that string actually contains a worksheet
name:

dim wks as worksheet
dim Temp as string

temp = "somestring"

set wks = nothing
on error resume next
set wks = worksheets(Temp)
on error goto 0

if wks is nothing then
'no worksheet with that name
else
'yes there is
end if



dallin wrote:

I'm trying to pass a text variable to the Worksheets() object where Temp is a
text variable, however, I must not be using the right syntax. I've used
Worksheets("Temp") and Worksheets(Temp) without success. What is the correct
syntax for this?
--
thanks, mc

--

Dave Peterson


--

Dave Peterson