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.

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.

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.

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

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

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