Open worksheet based on Cell Value

[Dukesford]

I wrote a macro that creates a new worksheet based on the month and year and renames it based on that and it won't create a new worksheet until the first of every month nor will it create it if the worksheets name exists. For instance next month when it is ran it will create a new worksheet named "9-07".

Later on a user needs to do some updates then calculate some things, so I want it after it is running this other code to select the newly created worksheet "9-07". I have this referenced in cell A1 and it would be nice if you could activate a worksheet based on a cell reference. Does anyone know how? So cell A1 = the new worksheet "9-07".

 

[Steel Monkey]

Assuming the new sheet name in in sheet1 in cell a1 try something like the following(changing the sheet names etc as needed) :

Option Explicit
Sub selectasheet()
Sheets("" & Sheets("Sheet1").Range("A1").Value).Select
End Sub

Let me know how it goes!

 

[zzjasonzz]

Steel,

What is the purpose of the "" & Sheets("Sheet1").Range("A1").Value

It should work fine using just Sheets(Sheets("Sheet1").Range("A1").value).Select

 

[Dukesford]

I am running into an error "runtime time error '9': Subscript out of range." Any suggestions? Also what are you assuming Sheet1 represents? When I am looking in Microsoft Visual Basic I see that it is actually Sheet 48 as of now, but with more testing before I get it to work smoothly that will increase, and as time goes on the number of sheets will increase based on the month. Also what does the "Option Explicit" mean, where should that be? I am using modules so does that matter?

 

[Dukesford]

Got it. I changed it a little and it no longer give me that error. Here is the code:

Sheets("" & Range("A1").Value).Select