Creating or going to a worksheet
 

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")

Creating or going to a worksheet
 

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.

"sdg8481" wrote in message
...
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")

Creating or going to a worksheet
 

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


--
Regards,
Tom Ogilvy


"sdg8481" wrote in message
...
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")

Creating or going to a worksheet
 

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

"Terry Aney" wrote:

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.

"sdg8481" wrote in message
...
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")