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.

 

[VoG]

That means that the sheet name in B2 doesn't exist. Check for leading or trailing spaces or mis-spellings in both the validation list and the sheet names.

The code works for me, by the way.

 

[lownote14]

I feel like such a fool. I have two words that have very similar names but haven't gotten to creating that sheet yet so it kept referencing a sheet that did not exist.

Many apologies for my simple solution, and thanks to you for holding my hand through this process.