Reference a worksheet that is not active?

[kazoogal]

Could someone please tell me how to reference another worksheet that is not the current and active worksheet? I'm sure its so simple...

I've assigned a macro to a button on sheet1 that calls a module to access data from sheet2...however, no matter what I try I cannot select the sheet2.

I'm creating a chart and therefore I need to get a data range from sheet2. I don't necessarily want to "activate" the sheet....if I don't have to.

I tried this but get a subscript out of range error...I do have a worksheet named "P01"

Dim mySheet As Worksheet
Set mySheet = Worksheets("P01&quot

 

[Zathras]

What you have should work.

You have the name as P-Zero-One in the macro. Is it possible that you have named the sheet P-Oh-One on the tab?

I am assuming that the sheet is in the same workbook, just on a different tab.

 

[kazoogal]

Yes....it is Pzero1 in BOTH the macro AND the worksheet...I even retyped it....and am still getting the subscript out of range error - AHHHHHH

Yes, the sheet is in the same workbook, different tab....

 

[kazoogal]

oooh...I just tried this....not sure why it works though

I changed the properties of the sheet and named it "shP01" and then I did:

Dim mySheet As Worksheet
Set mySheet = shP01

and it worked!

???

 

[Zathras]

What version of Excel are you using? (It works for me on Excel-97.)

In any case it is generally best to avoid using names for things that look like cell references such as "P01" -- Excel doesn't always do what you might expect in that case.