Bottom line: Learn 5 ways to use VBA macros to automate Excel tasks you do frequently at work.
Skill level: Beginner
Download the Excel File
Download the Excel file to follow along with the video.
What Can I Use Macros For in My Job?
This is a question I hear often. You might have heard you need to learn macros, but you're not exactly sure how they will help you. In the video above I explain 5 different ways to use macros to automate tasks in Excel.
We can use VBA macros to automate everything from simple tasks to complex reporting processes with multiple files. Here are the 5 topics covered in the video, along with the links mentioned to other articles and resources.
1. Data Related Tasks
Data related tasks include everyday Excel tasks to cleanup and format data. Examples covered in the video include.
- Remove Duplicates – Create a List of Unique Values
- Select or Delete Blank Rows
- Create formulas with macros – Percentage Change Formula
- Find the Last Used cell, row, or column in a sheet
- Apply formatting to raw data exports – Format Copier
2. Workbook Tasks
We can also automate tasks across worksheets and workbooks. This includes things like listing all the sheets, creating a table of contents, hiding specific sheets, opening and closing workbooks, and more. Here are some examples covered in the video.
- Table of Contents and TOC Gallery
- Save and Close All Open Workbooks
3. Pivot Table Tasks
Pivot tables are an amazing tool in Excel. They can also be time consuming to create, format, update, and maintain. Fortunately, we can automate just about every property and action with pivot tables using VBA macros. Here are a few examples from the video.
- Show Details Macro
- Automatic Default Number Formatting in Excel Pivot Tables
- Expand and Collapse Entire Pivot Table Fields
- How to Change Date Formatting for Grouped Pivot Table Fields
- The PivotPal Add-in – The entire add-in was built with VBA, and automates many repetitive pivot table tasks.
4. Userforms & Add-ins
Userforms are windows that open over Excel with interactive controls. They make our spreadsheets easier for other users to use. The userforms can be created and modified in the VB Editor.
Add-ins are installed on the users computer and usually contain custom ribbon tabs with buttons. This allows other users to run our macros on any open workbook. Checkout the Excel Campus Add-ins page for examples.
5. Process Automation
We can also automate more complex processes with macros & VBA. This includes creating systems to create, update, or modify several Excel files. I created many systems like this for forecasting and budgeting processes. Here is a link to an overview video of an application I call the File Manager. The File Manager allows you to run multiple macros on sets of files.
I explain how to create and use the File Manager step-by-step in module 11 of The VBA Pro Course.
Free Webinar on Macros & VBA
If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”. It's running all this week, and it's absolutely free to register.
During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started. I jump into Excel and the VB Editor and walk through how to write and run our first macro. Even if you have been using VBA for awhile, I'm sure you will learn some new tips.
Click here to learn more and register for the webinar
What Else Can We Use Macros For?
We can use macros for just about every task we do in Excel. This video doesn't even scratch the surface of what's possible, but hopefully it gets you thinking about some repetitive Excel tasks that you might want to automate.
Please leave a comment below with any questions or suggestions. Thank you! 🙂
Hi Jon,
I want to know how to properly do a SUMPRODUCT function in VBA.
I know of 2 methods, the Evaluate function and the other is using .Formula which returns the the formula of SUMPRODUCT in the target range.
Is there any other way i.e., simpler and easier to do?
Thank you.
Regards,
Ryan
Hi,
I just want to know how we may use macro for doing V look up in Excel.
Thanks,
Shyam
Hi Jon,
I was wondering if you have videos to your seminar because I missed it? I always loved Excel and now I want to learn to code the VBA so I can make my sheets better for work.
Thanks Al,
P.S. I have some knowledge in Programming
I have a doubt!
You can create macro for Excel that protects a range (5 columns) conditioned to multiple users.
Example:
User A has filled certain columns (A, B, C, D, E) –
The macro authorizes its completion because user “A” is allowed!
Already the user “X” can not make his name is not on the list of authorizations and is not allowed to change the cells of this range (columns).
Dear Jon,
Unfortunately, I couldn’t participate to your webinar about Marroc in July/Agust…
Is there a way to have access to some kind of replay of those sessions?
Thanks in advance.
Pierre
Macros… sorry.
Hello
I Have a question. Is it possible to use VBA to pay bills? I mean have a code to ba able to read bill ID and payment ID and pay the bill for factories? As you know factories have alot of bills for electricity or others.
i want learn macros with full of knowledge.
Hi, I am a beginner to excel n vba. I want to learn perfectly.how to understand n remember vba code? Give me a suggestion Please…
Also having excel functions also y do we need vba?
Thank you! i love your blog.
Hello,
I just would like to thank you for sharing this. Sometimes we forgot the work of people who gave some very insightful advices for free. I know this is a big work.
Thanks for that.
Can i use micros in a Samsung tablets???
Hi, I love the table of contents with thumbnail view, I was wondering if you can design this so the thumbnail views sit under predefined (a fixed number and fixed names) sections (like chapters of a book) and have a button at the end of each section on the contents which allows another sheet to be added automatically under that section that prompts the user to elect a copy of a pre existing worksheet so the formatting is maintained throughout the workbook. We would also like our branding theme throughout (white labelled).
Additionally, have a table of a section jump to (hyperlink buttons) at the top of the contents page to navigate between its sections.
Each Worksheet would also need a button to take the user back to the contents.
My view is we would start off with one template worksheet for each section and a button to add another.
The user could then build out the Workbook from there.
We would also like to have the flexibility to shrink the the thumbnails so the contents is easier to view.
Is this possible? How would one have this created?
The final item in the Wishlist would be if you could create an external Weblink not only to the Workbook (which would be saved in Office 365) but to a place within the workbook so weblinks could be given to others which would take them straight to the Worksheet relevant to them. However I’m told this is not possible!