Worksheet variable question

[lownote14]

I tried googling this with no success so I thought I would bring it to the attention of you fine excel gurus.

I need to know if you can call a worksheet, first by it's given name, instead of sheet 1. And two, use a variable to call that worksheet.

For example I have a data validation list of names, like Sam, Fred, Mike, and Roger. Those names correspond to worksheet with the name Sam, Fred, Mike and Roger. I would like it to looks something like this:

'This variable is what will contain the name (ie Sam, Fred etc...) _
'from the data validation.
Dim OCC as string
worksheet(OCC).range("a1").value
...

let me know if this doesn't make any sense.

 

[Andrew Fergus]

A technique I often use is to declare the worksheet names as constants like this:

Const wsSam as String = "Same"
Const wsFred as String = "Fred"
etc.

Then you call the sheets within the code like this:

ThisWorkbook.Sheets(wsSam).Range....etc.

I find the advantage of using constants is that by grouping them at the top of the code (outside the module) is that they are a) easy to find and change if necessary and b) by being a global constant all of the routines can use these values.

Instead of using Constants you can use variables with the Dim method (per your example) but you must then set the value of that variable, e.g.

Dim wsSam As String
...
wsSam = "Sam"
...
ThisWorkbook.Sheets(wsSam).Range....etc

There are other techniques (in particular for looping through all sheets) but this is the technique I often use for named / fixed sheets.

Andrew

 

[Andrew Fergus]

Re-reading your question I think I might have misunderstood you. Are you wanting to pull the name of another sheet from a sheet that has data validation and then pass that through another line of code?

For instance, sheet 1 cell A1 contains the name of another sheet (Sam or Fred) using a data validation list and you want to get somthing off that other sheet from cell B2:

Dim wsName as String
Dim AnotherValue as String

wsName = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
AnotherValue = ThisWorkbook.Sheets(wsName).Range("B2").Value

Andrew

 

[lownote14]

Thank you Andrew very helpful worked perfectly.

 

[lownote14]

Argh sorry for the double post, for some reason I can't find the edit post button.

Double Argh because I can't get this to work. I'm not sure exactly what I'm doing wrong so I figured I would just post my code, maybe that will help it make sense for everyone else too.

Sub initial_identifier()
    Dim OCC As String
        'this IF statement is to find whether
        'the cell D2 (a data validation drop down)
        'contains either nothing or something
    If Range("D2") = none Then
        'do nothing basically
    Else
        'this basically says for whatever is in
        'D2 find the sheet with the same name
        'then activate that sheet
        OCC = Range("D2").Value
        Sheets(OCC).Activate
        
    End If
    
End Sub

So for clarification this code is in sheet 1 so it will be calling to a different sheet. Right now I have it just activating the other sheet to see if I can get it to work. Later I want to be copying things from the "activated" work sheet.

Thank you in advanced for your patience.

 

[lownote14]

bump no response

 

[VoG]

Try

Sub initial_identifier()
    Dim OCC As String
        'this IF statement is to find whether
        'the cell D2 (a data validation drop down)
        'contains either nothing or something
    If Range("D2").Value <> "" Then
        'this basically says for whatever is in
        'D2 find the sheet with the same name
        'then activate that sheet
        OCC = Range("D2").Value
        Sheets(OCC).Activate
    End If
End Sub

 

[lownote14]

I'm sorry I should have been more clear about what wasn't working. "none" is actually an option and works. what doesn't seem to work for me is the line

Sub initial_identifier()
Dim OCC As String
        'this IF statement is to find whether
        'the cell D2 (a data validation drop down)
        'contains either nothing or something
    If Range("D2").Value <> "" Then
        'this basically says for whatever is in
        'D2 find the sheet with the same name
        'then activate that sheet
        OCC = Range("D2").Value
        [COLOR=Red]Sheets(OCC).Activate[/COLOR]
    End If
End Sub

For some reason when I run this if I replace sheets(OCC).Activate with something like sheets("sheet1").Activate it works, but I need the variable to work so that I can call to that specific work sheet chosen by the user.

I believe that the error given is that the sheet that I'm try to activate doesn't exist

 

[VoG]

Then it would need to be

If Range("D2").Value <> "none" Then

 

[lownote14]

Run time error '9'"
Subscript out of Range

That is the error I'm getting. I've modified with VoG's specifications because that makes sense. So now it looks like this:

Sub initial_identifier()
    Dim OCC As String
        'this IF statement is to find whether
        'the cell D2 (a data validation drop down)
        'contains either none or something else
        
    If Range("D2").Value <> "none" Then
        'this basically says for whatever is in
        'D2 find the sheet with the same name
        'then activate that sheet
        
        OCC = Range("D2").Value
        Sheets(OCC).Activate
        
    End If
    
End Sub