Rick,

Thanks for your help, it works great and using a module should be fine.
However, I have a couple of questions:

1) I forgot to mention this in my original post - Is there any way to
exclude weekends?

2) I used a command button to call the module because I wont be the only one
generating the reports. Is it possible to hide the command button in the
copied workbooks?

- Daniel

-----------
"Rick Rothstein (MVP - VB)" wrote:

I'm going to sleep for now, but I just wanted to tell you to check back here
over the next few days to see if I, or one of the other volunteers, have
come up with a method that doesn't involve adding a Module into each newly
created workbook file.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Okay, I don't know if this will be totally acceptable to you or not. It
requires you to add a Module (so as not to affect any existing code
procedures or modules) and that Module will be copied into each newly
created file where it will lie dormant and unused. It will affect nothing
to leave this Module in place, but it is something "extra" that will exist
in your new workbooks. Anyway, try this out on a copy of your template to
see if you like it or not.

To start, insert a new Module into your (copy of the) template, copy/paste
the code following my signature into the Module's code window, change the
directory assigned to the Path variable to point to a test directory for
your testing of this code, and then run the code in the Module (that is,
put your cursor in the code of the Module and then click the Run button).
You will be asked for the month you want to create the files for (if you
put a month number in that is less than the current month (like would
happen in December), the year for next year will be used instead of the
current year. Anyway, after you run the code, you will get a new file,
with the name you specified which has the year as indicated above, for
each day of the month (with the date placed in A1 of the first sheet).

One other note... I notice you used forward slashes in your directory
path... I'm guessing that is a network location... I don't have a network
here to test on, so my test was performed to a normal hard drive attached
to my system. NOTE that I add a slash after the directory name you
provided... it is mandatory that any path you provide to the Path variable
end in a slash of the appropriate type (back slash for normal hard drives
and, I am guessing, forward slash for network drives).

Rick

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth + 1, 0))
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
Next
End With
End Sub

I think the code below will do what you have asked. By the way, one note on
this code... I made a small change (correction) to what I posted earlier...
the month you type into the InputBox is to be the month you want to produce
your files for, **not** the current month. This will allow you to generate
any month, or as many months, in advance as you want to. Oh, and the code I
used for hiding the CommandButton assumes the CommandButton is an ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick


"Daniel" wrote in message
...
Rick,

Thanks for your help, it works great and using a module should be fine.
However, I have a couple of questions:

1) I forgot to mention this in my original post - Is there any way to
exclude weekends?

2) I used a command button to call the module because I wont be the only
one
generating the reports. Is it possible to hide the command button in the
copied workbooks?

- Daniel

-----------
"Rick Rothstein (MVP - VB)" wrote:

I'm going to sleep for now, but I just wanted to tell you to check back
here
over the next few days to see if I, or one of the other volunteers, have
come up with a method that doesn't involve adding a Module into each
newly
created workbook file.

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Okay, I don't know if this will be totally acceptable to you or not. It
requires you to add a Module (so as not to affect any existing code
procedures or modules) and that Module will be copied into each newly
created file where it will lie dormant and unused. It will affect
nothing
to leave this Module in place, but it is something "extra" that will
exist
in your new workbooks. Anyway, try this out on a copy of your template
to
see if you like it or not.

To start, insert a new Module into your (copy of the) template,
copy/paste
the code following my signature into the Module's code window, change
the
directory assigned to the Path variable to point to a test directory
for
your testing of this code, and then run the code in the Module (that
is,
put your cursor in the code of the Module and then click the Run
button).
You will be asked for the month you want to create the files for (if
you
put a month number in that is less than the current month (like would
happen in December), the year for next year will be used instead of the
current year. Anyway, after you run the code, you will get a new file,
with the name you specified which has the year as indicated above, for
each day of the month (with the date placed in A1 of the first sheet).

One other note... I notice you used forward slashes in your directory
path... I'm guessing that is a network location... I don't have a
network
here to test on, so my test was performed to a normal hard drive
attached
to my system. NOTE that I add a slash after the directory name you
provided... it is mandatory that any path you provide to the Path
variable
end in a slash of the appropriate type (back slash for normal hard
drives
and, I am guessing, forward slash for network drives).

Rick

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth + 1, 0))
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
Next
End With
End Sub

Thank you so much. I noticed one thing after few tests - after it creates all
of the workbooks it closes the master template and is left on the last day of
the month. Not a big issue, but the last day also has a Comman Button on it.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think the code below will do what you have asked. By the way, one note on
this code... I made a small change (correction) to what I posted earlier...
the month you type into the InputBox is to be the month you want to produce
your files for, **not** the current month. This will allow you to generate
any month, or as many months, in advance as you want to. Oh, and the code I
used for hiding the CommandButton assumes the CommandButton is an ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick

I think if you add this statement...

.Value = ""

immediately **before** the End With statement, it will clear out the last
date from A1 on your template file.

Rick


"Daniel" wrote in message
...
Thank you so much. I noticed one thing after few tests - after it creates
all
of the workbooks it closes the master template and is left on the last day
of
the month. Not a big issue, but the last day also has a Comman Button on
it.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think the code below will do what you have asked. By the way, one note
on
this code... I made a small change (correction) to what I posted
earlier...
the month you type into the InputBox is to be the month you want to
produce
your files for, **not** the current month. This will allow you to
generate
any month, or as many months, in advance as you want to. Oh, and the code
I
used for hiding the CommandButton assumes the CommandButton is an ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick

It still does the same thing - I'm going to play with the code a little and
see what I can do or mess up =]. Again, thanks for your help. This will save
me a lot of time.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think if you add this statement...

.Value = ""

immediately **before** the End With statement, it will clear out the last
date from A1 on your template file.

Rick


"Daniel" wrote in message
...
Thank you so much. I noticed one thing after few tests - after it creates
all
of the workbooks it closes the master template and is left on the last day
of
the month. Not a big issue, but the last day also has a Comman Button on
it.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think the code below will do what you have asked. By the way, one note
on
this code... I made a small change (correction) to what I posted
earlier...
the month you type into the InputBox is to be the month you want to
produce
your files for, **not** the current month. This will allow you to
generate
any month, or as many months, in advance as you want to. Oh, and the code
I
used for hiding the CommandButton assumes the CommandButton is an ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick

Just so I am clear... where is the last day of the month remaining at (which
file, which sheet, which cell)?

Rick


"Daniel" wrote in message
...
It still does the same thing - I'm going to play with the code a little
and
see what I can do or mess up =]. Again, thanks for your help. This will
save
me a lot of time.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think if you add this statement...

.Value = ""

immediately **before** the End With statement, it will clear out the last
date from A1 on your template file.

Rick


"Daniel" wrote in message
...
Thank you so much. I noticed one thing after few tests - after it
creates
all
of the workbooks it closes the master template and is left on the last
day
of
the month. Not a big issue, but the last day also has a Comman Button
on
it.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think the code below will do what you have asked. By the way, one
note
on
this code... I made a small change (correction) to what I posted
earlier...
the month you type into the InputBox is to be the month you want to
produce
your files for, **not** the current month. This will allow you to
generate
any month, or as many months, in advance as you want to. Oh, and the
code
I
used for hiding the CommandButton assumes the CommandButton is an
ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick

Here's what happens... It generates all of the reports, for example, 02-01-08
thru 02-29-08. Once it's finished the Master Template is closed (without my
doing) and the last day of the month is open, in this example 02-29-08. There
is a command button in the workbook for 02-29-08. If I exit without saving in
02-29-08 the command button won't be there.

Not sure if that makes any sense.

"Rick Rothstein (MVP - VB)" wrote:

Just so I am clear... where is the last day of the month remaining at (which
file, which sheet, which cell)?

Rick


"Daniel" wrote in message
...
It still does the same thing - I'm going to play with the code a little
and
see what I can do or mess up =]. Again, thanks for your help. This will
save
me a lot of time.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think if you add this statement...

.Value = ""

immediately **before** the End With statement, it will clear out the last
date from A1 on your template file.

Rick


"Daniel" wrote in message
...
Thank you so much. I noticed one thing after few tests - after it
creates
all
of the workbooks it closes the master template and is left on the last
day
of
the month. Not a big issue, but the last day also has a Comman Button
on
it.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think the code below will do what you have asked. By the way, one
note
on
this code... I made a small change (correction) to what I posted
earlier...
the month you type into the InputBox is to be the month you want to
produce
your files for, **not** the current month. This will allow you to
generate
any month, or as many months, in advance as you want to. Oh, and the
code
I
used for hiding the CommandButton assumes the CommandButton is an
ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick

Your Master Template closes all by itself? What is the extension on your
Master Template file? I did all my tests on files with .xls as the extension
and did not see what you are describing.

Rick


"Daniel" wrote in message
...
Here's what happens... It generates all of the reports, for example,
02-01-08
thru 02-29-08. Once it's finished the Master Template is closed (without
my
doing) and the last day of the month is open, in this example 02-29-08.
There
is a command button in the workbook for 02-29-08. If I exit without saving
in
02-29-08 the command button won't be there.

Not sure if that makes any sense.

"Rick Rothstein (MVP - VB)" wrote:

Just so I am clear... where is the last day of the month remaining at
(which
file, which sheet, which cell)?

Rick


"Daniel" wrote in message
...
It still does the same thing - I'm going to play with the code a little
and
see what I can do or mess up =]. Again, thanks for your help. This will
save
me a lot of time.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think if you add this statement...

.Value = ""

immediately **before** the End With statement, it will clear out the
last
date from A1 on your template file.

Rick


"Daniel" wrote in message
...
Thank you so much. I noticed one thing after few tests - after it
creates
all
of the workbooks it closes the master template and is left on the
last
day
of
the month. Not a big issue, but the last day also has a Comman
Button
on
it.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

I think the code below will do what you have asked. By the way, one
note
on
this code... I made a small change (correction) to what I posted
earlier...
the month you type into the InputBox is to be the month you want to
produce
your files for, **not** the current month. This will allow you to
generate
any month, or as many months, in advance as you want to. Oh, and
the
code
I
used for hiding the CommandButton assumes the CommandButton is an
ActiveX
one, **not** one from the Form's toolbar.

Sub SaveMonthlyDays()
Dim X As Long
Dim Mnth As Long
Dim Dte As Date
Dim Path As String
Path = "//rich5019/Common/West Region/SLC114/SLC CUT TIME
REPORT/"
Mnth = InputBox("Enter the month as a number", "Get Month")
With Worksheets(1).Range("A1")
For X = 1 To Day(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, 0))
If Weekday(DateSerial(Year(Now) - _
(Mnth < Month(Now)), Mnth, X), vbMonday) < 6 Then
Dte = DateSerial(Year(Now) - (Mnth < Month(Now)), Mnth, X)
.Value = Dte
.NumberFormat = "dd/mm/yyyy"
Worksheets(1).OLEObjects("CommandButton1").Visible = False
ThisWorkbook.SaveAs Path & Format(Dte, "dd-mm-yy")
End If
Next
Worksheets(1).OLEObjects("CommandButton1").Visible = True
End With
End Sub

Rick

Same .xls. I'm not sure why it's like that on my end. I'll give it a few more
tests to make sure.

"Rick Rothstein (MVP - VB)" wrote:

Your Master Template closes all by itself? What is the extension on your
Master Template file? I did all my tests on files with .xls as the extension
and did not see what you are describing.

Rick

Quick question for you. When you run it, does it update your current
workbook? For example, when I run it for 4, I can watch it save on the task
bar and the dates change in cell A1 until it gets to the last day where it
stays.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

Your Master Template closes all by itself? What is the extension on your
Master Template file? I did all my tests on files with .xls as the extension
and did not see what you are describing.

Rick

Yes, you can watch the individual saves take place in the status bar, and
the dates in A1 change; but when it reaches the last day of the specified
month, A1 is blanked out (because I added the .Value="" statement I posted
about earlier), the CommandButton becomes visible again and the worksheet
remains open. When I do close the template (manually), it asks me if I want
to save the changes to which I reply "No" (that way the template remains
every time I open it).

Rick


"Daniel" wrote in message
...
Quick question for you. When you run it, does it update your current
workbook? For example, when I run it for 4, I can watch it save on the
task
bar and the dates change in cell A1 until it gets to the last day where it
stays.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

Your Master Template closes all by itself? What is the extension on your
Master Template file? I did all my tests on files with .xls as the
extension
and did not see what you are describing.

Rick

Okay for some reason I commented out the .Value="". It works fine, thank you
for your time and help.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

Yes, you can watch the individual saves take place in the status bar, and
the dates in A1 change; but when it reaches the last day of the specified
month, A1 is blanked out (because I added the .Value="" statement I posted
about earlier), the CommandButton becomes visible again and the worksheet
remains open. When I do close the template (manually), it asks me if I want
to save the changes to which I reply "No" (that way the template remains
every time I open it).

Rick


"Daniel" wrote in message
...
Quick question for you. When you run it, does it update your current
workbook? For example, when I run it for 4, I can watch it save on the
task
bar and the dates change in cell A1 until it gets to the last day where it
stays.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

Your Master Template closes all by itself? What is the extension on your
Master Template file? I did all my tests on files with .xls as the
extension
and did not see what you are describing.

Rick

You are quite welcome... I am glad we got everything straightened out to
your satisfaction.

Rick


"Daniel" wrote in message
...
Okay for some reason I commented out the .Value="". It works fine, thank
you
for your time and help.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

Yes, you can watch the individual saves take place in the status bar, and
the dates in A1 change; but when it reaches the last day of the specified
month, A1 is blanked out (because I added the .Value="" statement I
posted
about earlier), the CommandButton becomes visible again and the worksheet
remains open. When I do close the template (manually), it asks me if I
want
to save the changes to which I reply "No" (that way the template remains
every time I open it).

Rick


"Daniel" wrote in message
...
Quick question for you. When you run it, does it update your current
workbook? For example, when I run it for 4, I can watch it save on the
task
bar and the dates change in cell A1 until it gets to the last day where
it
stays.

- Daniel

"Rick Rothstein (MVP - VB)" wrote:

Your Master Template closes all by itself? What is the extension on
your
Master Template file? I did all my tests on files with .xls as the
extension
and did not see what you are describing.

Rick