Automate Monthly Task

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

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
 
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?
Click to expand...

Correct, one file for each day in the month.
Do **each** of the (daily) files you are creating consist solely of a single
worksheet?
Click to expand...

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)?
Click to expand...

//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
 
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,

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

-----------
 
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
 
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
 
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
 
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
 
Just so I am clear... where is the last day of the month remaining at (which
file, which sheet, which cell)?

Rick


Daniel said:
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) said:
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
Click to expand...
Click to expand...
 
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) said:
Just so I am clear... where is the last day of the month remaining at (which
file, which sheet, which cell)?

Rick


Daniel said:
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) said:
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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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) said:
Just so I am clear... where is the last day of the month remaining at
(which
file, which sheet, which cell)?

Rick


Daniel said:
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

:

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


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

:

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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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
 
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
 
Okay for some reason I commented out the .Value="". It works fine, thank you
for your time and help.

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

Rick
 
Back
Top