Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make Worksheets Given a Certain YEAR
I copied this macro from one of the e-mail correspondences but somehow when I
run same it errors out with "sub or function not defined". This macro is suppose to create worksheets given a certain YEAR. The debugger is stopping at the "Application.StatusBar = D" Sub MakeYear() Dim SH As Worksheet Dim D As Date, Y As Long Set SH = ActiveSheet Y = Val(InputBox("Year:")) If Y < 2000 Then Exit Sub If Y 2100 Then Exit Sub Application.ScreenUpdating = False For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31) Application.StatusBar = D SH.Copy after:=Sheets(Sheets.Count) ActiveSheet.Range("A1").Value = D ActiveSheet.Name = Format(D, "mmm dd") Next Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make Worksheets Given a Certain YEAR
I don't understand why it would break at this command because all it's doing
is putting the date of the worksheet being currently created in the status bar. I'd simply delete the line because the date is only there fleetingly and serves no purpose within the macro other than to slow it down while it's adding the 365 worksheets. Mike "el zorro" wrote: I copied this macro from one of the e-mail correspondences but somehow when I run same it errors out with "sub or function not defined". This macro is suppose to create worksheets given a certain YEAR. The debugger is stopping at the "Application.StatusBar = D" Sub MakeYear() Dim SH As Worksheet Dim D As Date, Y As Long Set SH = ActiveSheet Y = Val(InputBox("Year:")) If Y < 2000 Then Exit Sub If Y 2100 Then Exit Sub Application.ScreenUpdating = False For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31) Application.StatusBar = D SH.Copy after:=Sheets(Sheets.Count) ActiveSheet.Range("A1").Value = D ActiveSheet.Name = Format(D, "mmm dd") Next Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make Worksheets Given a Certain YEAR
I deleted the line and still error'd out.
I don't know if this helps, the original error I mentioned below is a "compile error" according to the debugger. Thanks again Mike. "Mike H" wrote: I don't understand why it would break at this command because all it's doing is putting the date of the worksheet being currently created in the status bar. I'd simply delete the line because the date is only there fleetingly and serves no purpose within the macro other than to slow it down while it's adding the 365 worksheets. Mike "el zorro" wrote: I copied this macro from one of the e-mail correspondences but somehow when I run same it errors out with "sub or function not defined". This macro is suppose to create worksheets given a certain YEAR. The debugger is stopping at the "Application.StatusBar = D" Sub MakeYear() Dim SH As Worksheet Dim D As Date, Y As Long Set SH = ActiveSheet Y = Val(InputBox("Year:")) If Y < 2000 Then Exit Sub If Y 2100 Then Exit Sub Application.ScreenUpdating = False For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31) Application.StatusBar = D SH.Copy after:=Sheets(Sheets.Count) ActiveSheet.Range("A1").Value = D ActiveSheet.Name = Format(D, "mmm dd") Next Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make Worksheets Given a Certain YEAR
The code ran ok for me (after I removed the 's).
Try copying and pasting again. and while you're testing, you may want to use: For D = DateSerial(Y, 1, 1) To DateSerial(Y, 1, 31) (only 31 new sheets instead of 365 or 366). Ps. The number of worksheets in a workbook is limited by your pc's memory. You may have trouble with lots and lots of sheets. el zorro wrote: I copied this macro from one of the e-mail correspondences but somehow when I run same it errors out with "sub or function not defined". This macro is suppose to create worksheets given a certain YEAR. The debugger is stopping at the "Application.StatusBar = D" Sub MakeYear() Dim SH As Worksheet Dim D As Date, Y As Long Set SH = ActiveSheet Y = Val(InputBox("Year:")) If Y < 2000 Then Exit Sub If Y 2100 Then Exit Sub Application.ScreenUpdating = False For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31) Application.StatusBar = D SH.Copy after:=Sheets(Sheets.Count) ActiveSheet.Range("A1").Value = D ActiveSheet.Name = Format(D, "mmm dd") Next Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make Worksheets Given a Certain YEAR
Dave,
I re-pasted the macro and did the change you suggested and it worked just fine. Much appreciated it. Mike H, Thank you also. "Dave Peterson" wrote: The code ran ok for me (after I removed the 's). Try copying and pasting again. and while you're testing, you may want to use: For D = DateSerial(Y, 1, 1) To DateSerial(Y, 1, 31) (only 31 new sheets instead of 365 or 366). Ps. The number of worksheets in a workbook is limited by your pc's memory. You may have trouble with lots and lots of sheets. el zorro wrote: I copied this macro from one of the e-mail correspondences but somehow when I run same it errors out with "sub or function not defined". This macro is suppose to create worksheets given a certain YEAR. The debugger is stopping at the "Application.StatusBar = D" Sub MakeYear() Dim SH As Worksheet Dim D As Date, Y As Long Set SH = ActiveSheet Y = Val(InputBox("Year:")) If Y < 2000 Then Exit Sub If Y 2100 Then Exit Sub Application.ScreenUpdating = False For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31) Application.StatusBar = D SH.Copy after:=Sheets(Sheets.Count) ActiveSheet.Range("A1").Value = D ActiveSheet.Name = Format(D, "mmm dd") Next Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Make Worksheets Given a Certain YEAR
I'm not sure why, but sometimes copying from a web page can bring extra
characters with it. It doesn't seem to happen in the communities web pages very often. So you got lucky <vbg. el zorro wrote: Dave, I re-pasted the macro and did the change you suggested and it worked just fine. Much appreciated it. Mike H, Thank you also. "Dave Peterson" wrote: The code ran ok for me (after I removed the 's). Try copying and pasting again. and while you're testing, you may want to use: For D = DateSerial(Y, 1, 1) To DateSerial(Y, 1, 31) (only 31 new sheets instead of 365 or 366). Ps. The number of worksheets in a workbook is limited by your pc's memory. You may have trouble with lots and lots of sheets. el zorro wrote: I copied this macro from one of the e-mail correspondences but somehow when I run same it errors out with "sub or function not defined". This macro is suppose to create worksheets given a certain YEAR. The debugger is stopping at the "Application.StatusBar = D" Sub MakeYear() Dim SH As Worksheet Dim D As Date, Y As Long Set SH = ActiveSheet Y = Val(InputBox("Year:")) If Y < 2000 Then Exit Sub If Y 2100 Then Exit Sub Application.ScreenUpdating = False For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31) Application.StatusBar = D SH.Copy after:=Sheets(Sheets.Count) ActiveSheet.Range("A1").Value = D ActiveSheet.Name = Format(D, "mmm dd") Next Application.StatusBar = False Application.ScreenUpdating = True End Sub Thanks. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i calculate what i make in a year into an hour? | Excel Worksheet Functions | |||
how to make a rolling calender where hours roll off after a year | Excel Discussion (Misc queries) | |||
how do I make changes in several worksheets at once? | Excel Worksheet Functions | |||
how do I make changes in several worksheets at once? | Excel Discussion (Misc queries) | |||
How would I make a calendar template in Excel for the year 2006?? | Excel Worksheet Functions |