Activating a worksheet through use of a variable

Hi

I'm trying to activate a worksheet based on its name which has been obtained
from a Vlookup and passed to a variable(see below). However, whichever way I
try to activate the sheet it doesn't seem to be able to understand what's in
the variable. In the first case, it seems to think the variable is a number
('Subscript out of range') and it the second, it just ignores it. Any ideas
on how I can do this ?


Sub auto_open()

Dim PTuName, PTinf As String
Dim wks As Object

PTuName = Get_User_Name

PTinf = Application.WorksheetFunction _
.VLookup(PTuName, Worksheets("Names").Range("A2:B3"), 2)

' Activate the appropriate staff sheet

Worksheets(PTinf).Activate ' Case 1

'For Each wks In Worksheets 'Case 2
' If wks.Name = PTinf Then wks.Activate
'Next wks

End Sub

Ta
--
Cads
It''''s all meaningless in the end

To help you debug, enter
MSGBOX(PTinf) just before your Activate
--
Gary's Student


"Cads" wrote:

> Hi
>
> I'm trying to activate a worksheet based on its name which has been obtained
> from a Vlookup and passed to a variable(see below). However, whichever way I
> try to activate the sheet it doesn't seem to be able to understand what's in
> the variable. In the first case, it seems to think the variable is a number
> ('Subscript out of range') and it the second, it just ignores it. Any ideas
> on how I can do this ?
>
>
> Sub auto_open()
>
> Dim PTuName, PTinf As String
> Dim wks As Object
>
> PTuName = Get_User_Name
>
> PTinf = Application.WorksheetFunction _
> .VLookup(PTuName, Worksheets("Names").Range("A2:B3"), 2)
>
> ' Activate the appropriate staff sheet
>
> Worksheets(PTinf).Activate ' Case 1
>
> 'For Each wks In Worksheets 'Case 2
> ' If wks.Name = PTinf Then wks.Activate
> 'Next wks
>
> End Sub
>
> Ta
> --
> Cads
> It''''s all meaningless in the end

Thanks but I've already done this and discovered that the value I want is in
the variable (that is it's the name on the sheet).

My problem is that (i) the Worksheets function doesn't accept it as the name
of the sheet and (ii) the If statement doesn't equate the content of PTinf
with the worksheet name (wks.Name). If I type in the name in both cases (eg
"Sheet99") then it works fine.
--
Cads
It's all meaningless in the end


"Gary''s Student" wrote:

> To help you debug, enter
> MSGBOX(PTinf) just before your Activate
> --
> Gary's Student
>
>
> "Cads" wrote:
>
> > Hi
> >
> > I'm trying to activate a worksheet based on its name which has been obtained
> > from a Vlookup and passed to a variable(see below). However, whichever way I
> > try to activate the sheet it doesn't seem to be able to understand what's in
> > the variable. In the first case, it seems to think the variable is a number
> > ('Subscript out of range') and it the second, it just ignores it. Any ideas
> > on how I can do this ?
> >
> >
> > Sub auto_open()
> >
> > Dim PTuName, PTinf As String
> > Dim wks As Object
> >
> > PTuName = Get_User_Name
> >
> > PTinf = Application.WorksheetFunction _
> > .VLookup(PTuName, Worksheets("Names").Range("A2:B3"), 2)
> >
> > ' Activate the appropriate staff sheet
> >
> > Worksheets(PTinf).Activate ' Case 1
> >
> > 'For Each wks In Worksheets 'Case 2
> > ' If wks.Name = PTinf Then wks.Activate
> > 'Next wks
> >
> > End Sub
> >
> > Ta
> > --
> > Cads
> > It''''s all meaningless in the end