Selecting sheet with VB

[RobN]

I've used the following to try and have VB select a sheet dependant on the
value in d2.
Dim ss As Variant
Set ss = Sheet60.Range("d2")

How do I apply that now to select that sheet? I thought something like
sheet(ss).select would do it, but it doesn't work

Sheets(ss).select does work, but doesn't go to the correct sheet number,
whereas Sheet(ss) brings up a "Sub or Function not defined" error.

Rob

 

[Beth Melton]

First you need to use an explicit variable declaration - using Variant kinda
defeats the purpose. If you are trying to create a worksheet name then
you need to use a String data type.

What I'm not following is how the sheet names are created. Are you working
with the standard sheet names such as Sheet1, Sheet2, etc and does cell D2
have only a value such as 1, 2, 3? If that's the case then you need to
concatenate "Sheet" with your ss variable. For example: ss="Sheet" &
Sheet60.Range("D2")

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx

 

[RobN]

Thanks Beth. I've finally sorted out the problem. I had some more help
from the Excel newsgroup.

Rob

 

[Beth Melton]

Glad to hear you have it sorted.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP
https://mvp.support.microsoft.com/profile/Melton
What is a Microsoft MVP? http://mvp.support.microsoft.com/gp/mvpfaqs

Guides for the Office 2007 Interface:
http://office.microsoft.com/en-us/training/HA102295841033.aspx