| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Worksheet | Excel Discussion (Misc queries) | |||
| Creating a dynamic Worksheet name? | Excel Programming | |||
| Creating a new worksheet? | Excel Programming | |||
| Creating a Worksheet in VBA | Excel Programming | |||
| Creating a new worksheet from another | Excel Programming | |||
Linear Mode
