Indirect Sheet Ref in VBA

[somesoldiers]

Hi All

instead of Sheets("Jan 2011").Select would like to use INDIRECT ref "'"&$P$8"'!" in code instead of Jan 2011 so that when user picks month from dropp down the macro will know to reference this sheet- any ideas appreciated

Thanks
Noel

 

[VoG]

Hi. Try

Sheets(Range("P8").Value).Select

 

[JackDanIce]

Would this work?

Dim iSheet as String
iSheet = Range("P8")
Sheets(iSheet).Select

 

[somesoldiers]

Thanks Guys

Just 1 thing, my sheets are named Jan 2011, Feb 2011 etc have a drop down in P8 with all months formatted as mmm yyyy but vba is reading as 01/01/2011 so is not picking up sheet. Is there a quick way round this?

 

[VoG]

Try

Sheets(Range("P8").Text).Select

 

[somesoldiers]

Perfect, thanks Guys