Hello,

I have an annoying monthly task that I am trying to automate. Each month I
have to copy a workbook for each day in the month, enter the date in A1 and
then save the file as the date (DD-MM-YY).

I have no real VBA experience besides doing some tutorials here and there. I
was thinking of creating a userform with a textbox for the file path (in case
it ever changes), 2 dropdown boxes - month and year, and a button to create
the workbooks.

Any suggestions on where to begin? I've been searching - maybe I'm using the
wrong key words, but I haven't been successful.

To clarify, you are creating one individual file for **each** day in a month
(that is, you are **not** creating a single workbook consisting of as many
worksheets as there are days in the month), right?

Do **each** of the (daily) files you are creating consist solely of a single
worksheet?

You have a master template file from which you make your copies, right?

I am assuming all of the files go into a single directory... what is the
directory (I'll hard-code it into my response)?

Rick


"Daniel" <(E-Mail Removed)> wrote in message
news:FDEE7284-DC33-48ED-8911-(E-Mail Removed)...
> Hello,
>
> I have an annoying monthly task that I am trying to automate. Each month I
> have to copy a workbook for each day in the month, enter the date in A1
> and
> then save the file as the date (DD-MM-YY).
>
> I have no real VBA experience besides doing some tutorials here and there.
> I
> was thinking of creating a userform with a textbox for the file path (in
> case
> it ever changes), 2 dropdown boxes - month and year, and a button to
> create
> the workbooks.
>
> Any suggestions on where to begin? I've been searching - maybe I'm using
> the
> wrong key words, but I haven't been successful.

>To clarify, you are creating one individual file for **each** day in a month
> (that is, you are **not** creating a single workbook consisting of as many
> worksheets as there are days in the month), right?

Correct, one file for each day in the month.

> Do **each** of the (daily) files you are creating consist solely of a single
> worksheet?

There are two worksheets.

> You have a master template file from which you make your copies, right?

Yes

> I am assuming all of the files go into a single directory... what is the
> directory (I'll hard-code it into my response)?

//rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT

Thanks for any help.

- Daniel

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




"Daniel" <(E-Mail Removed)> wrote in message
news:6D76169E-96E4-4B88-BFF5-(E-Mail Removed)...
> >To clarify, you are creating one individual file for **each** day in a
> >month
>> (that is, you are **not** creating a single workbook consisting of as
>> many
>> worksheets as there are days in the month), right?
>
> Correct, one file for each day in the month.
>
>> Do **each** of the (daily) files you are creating consist solely of a
>> single
>> worksheet?
>
> There are two worksheets.
>
>> You have a master template file from which you make your copies, right?
>
> Yes
>
>> I am assuming all of the files go into a single directory... what is the
>> directory (I'll hard-code it into my response)?
>
> //rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT
>
> Thanks for any help.
>
> - Daniel
>

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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
>
>
> "Daniel" <(E-Mail Removed)> wrote in message
> news:6D76169E-96E4-4B88-BFF5-(E-Mail Removed)...
>> >To clarify, you are creating one individual file for **each** day in a
>> >month
>>> (that is, you are **not** creating a single workbook consisting of as
>>> many
>>> worksheets as there are days in the month), right?
>>
>> Correct, one file for each day in the month.
>>
>>> Do **each** of the (daily) files you are creating consist solely of a
>>> single
>>> worksheet?
>>
>> There are two worksheets.
>>
>>> You have a master template file from which you make your copies, right?
>>
>> Yes
>>
>>> I am assuming all of the files go into a single directory... what is the
>>> directory (I'll hard-code it into my response)?
>>
>> //rich5019/Common/West Region/SLC114/SLC CUT TIME REPORT
>>
>> Thanks for any help.
>>
>> - Daniel
>>
>

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)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:34777330-FBAC-4CB9-8600-(E-Mail Removed)...
> 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)" <(E-Mail Removed)> wrote
>> in
>> message news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:E88492F8-9036-45F7-A599-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:E88492F8-9036-45F7-A599-(E-Mail Removed)...
> > 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
>
>