Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Activating a worksheet through use of a variable
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Activating a worksheet through use of a variable
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Worksheet to Worksheet | Excel Worksheet Functions | |||
Using VBA to create a new worksheet, and then target new worksheet | Excel Discussion (Misc queries) | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
copyright and worksheet protection | Excel Discussion (Misc queries) |