Summary: Why invest time learning VBA if it doesn’t make your life easier?
When I encounter a boring and repetitive task at work, I figure out how to get rid of it before it drives me insane.
I can either try – and fail – to convince my boss that the task is pointless, or I can impress my boss by writing a macro that will do the work for me. Guess what I do?
In this case study I explain how I used Excel VBA macros to do a repetitive 18 step task in just 10 seconds.
Download the Sample Workbook
Download the sample file with VBA code and the data workbooks to email
Each month I receive a financial performance workbook from each of my company’s subsidiaries. We have 50 subsidiaries, so I receive 50 workbooks per month.
Each workbook contains the following three sheets:
At one point, I had to manually save the “Investments” sheet in the same directory as the workbook, and then send this sheet via email to one of my colleagues. I had to repeat this process for each of the 50 subsidiaries.
I know this task seems pointless. I was spamming my colleague’s mailbox instead sharing the files via the server. Since my company often grew paranoid about server access rights, though, I had to find another solution.
Unfortunately, I wasn’t allowed to skip this task. Since I had already been experimenting with VBA, I bet you know my next thought. This cumbersome process needed programming! I got to work!
When I decided to automate this process with a macro, I first decided on the basic logic and rules:
After laying out the requirements, I had to figure out how to write code to meet them.
As I already mentioned, a macro would save loads of time. In daily work, I hate to waste energy on pointless processes. Who does?
However, there were a few parts of the process I couldn’t easily automate:
That being said, I focused my macro on saving the “Investment” sheet in the appropriate folder and emailing it.
Without the macro, the whole process looked like this:
The manual process took approximately 50 seconds. That may not seem like much, but remember that I had to do this for 50 companies! That is almost an hour of time wasted!
My company paid me for an hour of clicking and browsing on my computer and spamming my colleagues!
As you can see, I can’t stand useless processes. I needed a macro that automatically performed the 18 steps with only a couple of clicks!
With my new macro I can simply select the “Investment” sheet, bring up the Macro Dialog Box (ALT + F8) and run the “Sheet Saver” macro. Done in less than 10 seconds
It’s time to get to the interesting part! Don’t feel overwhelmed, though. I will walk you through the code bit by bit.
As all programmers should, I started by declaring the variables:
Public CountryNr As String
Public CountryName As String
Public Year As String
Public ActMonth As String
Dim ActPath As String
Dim Msg As String
As you can see, I publicly declared some variables before the beginning of the “Sheet Saver” macro. I did this because, although I wrote the “Sheet Saver” and “Send Mail” macros separately, they shared the public variables.
CountryNr = ActiveWorkbook.Sheets("Financials").Range("B2").Value
ActPath = ActiveWorkbook.Path
Year = "2019_"
After declaring the variables, I assigned values to each.
The next snippet gets more interesting:
'Choosing CountryName of list
Select Case CountryNr
CountryName = "Middle Earth"
CountryName = "Narnia"
CountryName = "Westeros"
'Error handling, if number is not in the list
MsgBox ("Error! No country found with the given number!")
As you can see, I used the “Select Case” method to connect the country numbers to their short names. I hard-coded the names since I didn’t anticipate them changing.
If the “Case” (e.g., 11, 12, or 13) isn’t found, the MsgBox will alert the user that no country matches their number.
I determined the period using the below code:
'Choosing the right month: the month before the actual month
'If it's January, take month 12
ActMonth = month(Now) - 1
If ActMonth = "0" Then ActMonth = "12"
'If month is only 1-digit long, convert it to 2-digits long
If ActMonth < 10 Then ActMonth = "0" & ActMonth
After the macro sets the “ActMonth” equal to the period, I make sure it’s the right month by asking the user to confirm via a MsgBox:
'Display question about the right month
Msg = "The document will be saved as " & Year & CountryNr & "_" & _
CountryName & "_Invest_" & ActMonth & ".xlsx" & " for month " & _
ActMonth & vbNewLine & _
"Is this the right month?"
'If the defined month is wrong, the correct one must be typed in
If MsgBox(Msg, vbYesNo, "Period") <> vbYes Then
ActMonth = InputBox("Please, type in the right period (month)", _
If the user clicks “No” on the MsgBox to indicate that the month is incorrect, I use an InputBox to allow the user to input the correct month. I then save the copy of active sheet to the path as a new workbook:
'Save the active sheet in the same directory where ActiveWorkbook is,
'with the specified naming logic
ActiveWorkbook.SaveAs Filename:= _
ActPath & "\" & Year & CountryNr & "_" & CountryName & _
"_Invest_" & ActMonth & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
I created the above code snippet by recording it using the Macro Recorder, modifying the Filename property, and replacing hardcoded values with my own variables.
After the macro saves the sheet, it calls the “Send Mail” macro:
'Displaying E-mail form before sending it out.
'If you don't have Outlook installed, don't worry!
'The Send_Mail function won't run, and you are done with saving the sheet.
'Close the copied sheet, save changes
Before digging into the “Send Mail” macro, note that after the “Send Mail” macro runs, the macro closes the copied sheet. The word “True” after the “Close” command means that the code will save the changes before closing the workbook.
Private Sub Send_Mail()
'This example sends the last saved version of the ActiveSheet,
'via an Outlook Object
'Microsoft Office Library needs to be enabled under Tools -> References
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
I started with “Private Sub” to indicate that the routine can only work within the current module. It shouldn’t run until after the first macro saves the “Investments” sheet as a new workbook and activates it.
The code only works if you’ve enabled the Office objects by following the below steps in the VBA Editor:
The version number (e.g., “16.0”) depends on your current Office version, but the rest of the name stays the same. Make sure to activate the reference object before running the macro!
The next lines of code set the email’s properties:
On Error Resume Next
.To = "firstname.lastname@example.org"
.CC = ""
.BCC = ""
.Subject = Year & CountryNr & "_Invest_" & CountryName & "_" & _
ActMonth & ".xlsx"
.Body = "Dear Mr. Harry Potter," & vbNewLine & _
vbNewLine & _
"Attached you can find last month's investments from " & _
CountryName & "." & vbNewLine & _
vbNewLine & _
"Best Regards," & vbNewLine & _
vbNewLine & _
I think they’re relatively self-explanatory. When I built the macro, I recorded code to send an email and then replaced hard-coded values with variables.
In the “.Body” section, you can see the email’s text.
After the macro builds the emails, it attaches the workbook created from the “Financials” sheet:
.Display 'or use .Send
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
The macro uses “.Display” to show me the email instead of automatically sending it. This allows me to give it a once-over before sending it.
I knew I could easily change “.Display” to “.Send” after running the macro several times and confirming it worked properly. This would shorten my process even more.
I love automating manual processes using macros!
In all the macros I write, I follow this basic process:
I built the “Sheet Saver” and “Send Mail” macros this way. Doing so helps build structured and easy-to-debug code. Over time you’ll learn that debugging often takes more time than coding!
Don’t be afraid to try Macro Recorder or solutions from internet forums. And if sample code doesn’t meet all your needs, experiment!
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.