Creating or going to a worksheet

[Guest]

Hi,

I'm trying to create a button that when a month is selected (using a
drop-down box) and the button is clicked it either

a) if the sheet currently exists - goes to the chosen tab OR

b) if the sheet does not exist - creates a copy of the master sheet and
renames it the chosen month.

I've got so near with the following coding its just something letting me
down, but i don't know what. Please help

If Worksheets(Format(Cells("10", "B"), "mmmm yy")).Visible Then

'Goto
ActiveWorkbook.Worksheets(Format(Cells("10", "B"), "mmmm yy")).Activate

Else

'create
ActiveWorkbook.Worksheets("MASTER").Copy After:=Worksheets("MENU")
ActiveSheet.Name = Format(Cells("10", "B"), "mmmm yy")
Worksheets(Format(Cells("10", "B"), "mmmm yy")).Range("Y1") =
Format(Worksheets("Menu").Range("B10"), "mmmm yyyy")

 

[Terry Aney]

Let me know where it 'fails', I might have some ideas. For example, you
can't create the sheet, you can't change the name, you can't set the value,
etc.

 

[Tom Ogilvy]

Dim s as String, sh as Worksheet
s = Format(Cells("10", "B"), "mmmm yy")
on Error Resume Next
set sh = Worksheets(s)
On error goto 0
if sh is nothing then
worksheets("Master").Copy After:=Worksheets(Worksheets.count))
Else
sh.Activate
End if

 

[Guest]

Hi,

It seems to faile when creating a new sheet, as it finds an existing
sheetperfectly well, but if the sheet does exist it comes up with "Run-time
Error '9': Subscript out of range"

and the if statement appears to be the problem

Thanks