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