Worksheets() Object

[dallin]

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(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

[dallin]

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
>

[Tim Williams]

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

Tim


"dallin" <[email protected]> wrote in message news:[email protected]...
> 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.
>>

[Dave Peterson]

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

[dallin]

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
>