Decorative image on VBA macros in Excel.

How to Automate Tasks in Excel with VBA Macros (Beginner’s Guide)

   April 14, 2025  8 Comments on How to Automate Tasks in Excel with VBA Macros (Beginner’s Guide)  Posted in Excel

VBA Macros in Excel allow us to automate routines tasks. We all have been in situations where we find ourselves performing the same Excel tasks? Sometimes boss asked us to create monthly reports that feel excruciatingly repetitive, or format headers, or copy and paste data all the time.

If this is the case, you are not alone. The good news is that we don’t have to continue doing it manually. We can save hours of work each week by automating these tasks with a little magic known as VBA macros.

Better yet, we can begin using VBA Macros in Excel without knowing how to program. With straightforward language, practical examples, and a ton of beginner-friendly advice, this guide will take you through everything you need to know.

What is a Macro in Excel?

Fundamentally, a macro is a set of commands that Excel uses to carry out operations automatically. Assume if we could inform Excel the following:

“Hey Excel, please make the first row bold, put color on the header, re-arrange the columns, and then forward it to my boss each time I open this file.”

That would be amazing right? That’s exactly what macros allow us to do. Excel (and other Microsoft Office products) comes with a programming language called VBA, or Visual Basic for Applications, which powers macros.

However, if you have never coded before, don’t worry. Excel’s basic recording tools make it incredibly simple to begin using macros.

Why Automate Tasks with VBA Macros in Excel?

People who do some repetitive tasks absolutely love VBA Macros in Excel. The following are some genuine explanations for the same:

  • Save time: by completing hours of work in a matter of seconds.
  • Prevent Errors: Automations are not distracted or weary. Means they can perform the same task repeatedly without errors.
  • Standardize Workflows: Make certain that tasks are completed consistently.
  • Manage Big Data: Easily work with thousands of rows.
  • Achieve peace of mind: concentrate on thinking rather than clicking.

You’ll definitely question why you didn’t start sooner after automating a few tasks!

How to Create a VBA Macro in Excel

We will tell you two simple methods to create our first macro:

1. Record Your Actions (No Coding Needed)

We will click around normally and perform our routine task just once, and Excel will keep recording everything we did to complete that task. From next time, we will just click on a button and Excel will do all those steps in a second.

2. Write VBA Code Manually (More Flexible)

Here we will type the instructions our self, which gives us the full control over steps.

In this article, we’ll explore both options.

Recording Your First VBA Macro in Excel (Step-by-Step)

Assume that we have a task of preparing a sales report and we do it every month. We need to always do the following steps to make the sales report:

  • First we make the header bold.

  • Then we fill blue background in the header.

  • At last we adjust the columns to fit the length of content.

Now rather than doing this manually each time, we will record a macro. Please note steps may differ a little based on the version of Excel you are using.

Steps:

  1. Enable the Developer Tab (if it is not already enabled):

    • Go to File → Options → Customize Ribbon → then tick the Developer checkbox.

  2. Start Recording:

    • Click Developer → Record Macro.

    • Put a name on your macro to remember what is is for. For e.g.FormatSalesReport.

    • (Optional) We can assign a shortcut key (e.g., Ctrl+Shift+F) to do it even faster..

  3. Perform the Actions:

    • Now we will do our 3 steps mentioned above manually which start from making the header row bold.

    • Then we will fill blue background in the header.

    • At last we will adjust the columns to fit the length of content.

  4. Stop Recording:

    • Now we go back to Developer → Stop Recording.

And that is it! We just recorded our first macro!

From whenever we need to do this task again, we will only need to run the macro we created and Excel will do everything for us in seconds.We will not have to manually format the sheet anymore. Imagine how much time it will saveus if we were to perform this task 500 times in a month.

Where is the Macro Code Stored?

Excel in fact writes the VBA code on our behalf behind the scenes after we complete recording a macro.

Follow these steps to see the code yourself:

  •  HitAlt + F11 – this will open the VBA Editor.

  • Look for Modules – Module1. This is where our Macro is stored.

It will look something like this –

Sub FormatSalesReport()
Range("A1:C1").Font.Bold = True
Range("A1:C1").Interior.Color = RGB(0, 112, 192)
Columns("A:C").AutoFit
End Sub

Don’t worry if this seems a little unclear at this point. You will gradually become accustomed to reading and even editing this code as you use macros more.

Writing Your First VBA Macro (Step-by-Step)

For easy tasks, recording macros works perfectly well and is easy to use as well. However, writing VBA manually by hand will give you more control. This straightforward macro removes old data from a sheet :

Sub ClearOldData()
' Clears content from A2 to D100
Range("A2:D100").ClearContents
MsgBox "Old data cleared successfully!"
End Sub

What’s happening here?

  • Sub ClearOldData() will start the macro.

  • Range("A2:D100").ClearContents will clear the selected data range.

  • MsgBox will pop up a message box to indicate to us that the work is completed.

Now, to run it:
Go to Developer → Macros → select ClearOldData → Run.

Real-World Examples of VBA Macros in Excel

It is time to look at practical cases where people use macros in offices and business.

1. Auto-Send Emails from Excel Using Outlook

Let’s say that we need to send a report automatically from Excel.

This macro creates a draft email for you:

Sub SendWeeklyReport()
Dim OutlookApp As Object
Dim MailItem As ObjectSet OutlookApp = CreateObject(“Outlook.Application”)
Set MailItem = OutlookApp.CreateItem(0)With MailItem
.To = “manager@example.com”
.Subject = “Weekly Sales Report”
.Body = “Hi Team,” & vbNewLine & “Please find attached the latest sales report.”
.Display ‘ Change to .Send to send immediately
End With
End Sub

And this is not it. We can expand this macro to attach files automatically!

2. Consolidate Data from Multiple Sheets

Copying and pasting by hand can be a pain if we have more than ten sheets with similar data structures. But all of the sheets will be combined into a single “Master” sheet by this macro:

This macro will combine all sheets into one “Master” sheet:

Sub CombineSheets()
Dim ws As Worksheet
Dim masterSheet As Worksheet
Dim lastRow As Long
Dim pasteRow As LongSet masterSheet = ThisWorkbook.Sheets(“Master”)pasteRow = 1For Each ws In ThisWorkbook.Sheets
If ws.Name <> “Master” Then
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.Range(“A1:C” & lastRow).Copy masterSheet.Cells(pasteRow, 1)
pasteRow = masterSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
End If
Next ws

MsgBox “All sheets combined successfully!”
End Sub

This eliminates the need of any manual work and everything is merged automatically.

3. Automatically Format New Data

We can use a macro to quickly clean up poorly formatted raw data that we receive from clients or other departments on a regular basis. This is a brief formatting macro:

Sub FormatRawData()
Rows("1:1").Font.Bold = True
Rows("1:1").Interior.Color = RGB(0, 176, 240)
Columns("A:Z").AutoFit
Range("A2:Z1000").Borders.Weight = xlThin
MsgBox "Data formatted successfully!"
End Sub

Here in just one click, we can:

  • Make the header bold.

  • Add color in the background.

  • Auto-fit all columns.

  • Add professional looking borders around our data.

Top 5 Easy VBA Macros in Excel Projects for Beginners

We have listed below five quick macro projects / exercises we can try today:

Task Macro Idea
Daily Report To open our daily report template and format it automatically.
Reminder Popup To display a pop-up when we open a file to remind us of deadlines.
Sheet Cleaner To remove empty rows and columns in one click.
File Organizer To move or copy files to specific folders based on their names.
Auto-Save To save a backup copy of our workbook every 10 minutes.

For the first problem, here’s a simple VBA code example you can adapt:

Sub OpenAndFormatDailyReport()

Dim wb As Workbook
Dim ws As Worksheet
Dim reportPath As String

‘ Set the path to your daily report template
reportPath = “C:\Path\To\Your\DailyReportTemplate.xlsx” ‘ <– update this path

‘ Open the workbook
Set wb = Workbooks.Open(reportPath)

‘ Assuming you want to format the first sheet
Set ws = wb.Sheets(1)

‘ Example Formatting
With ws
‘ Auto-fit all columns
.Cells.EntireColumn.AutoFit

‘ Set font for the whole sheet
.Cells.Font.Name = “Calibri”
.Cells.Font.Size = 11

‘ Set header row (first row) formatting
With .Rows(1)
.Font.Bold = True
.Interior.Color = RGB(200, 200, 255) ‘ Light blue background
.HorizontalAlignment = xlCenter
End With

‘ Add borders to used range
With .UsedRange.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End With

‘ Save and close
wb.Save
wb.Close

MsgBox “Daily report formatted successfully!”, vbInformation

End Sub

Tips for Beginners in VBA Macros in Excel

  • Save as .xlsm: Always remember to save macro-enabled files as .xlsm.

  • Use the Recorder First: Secondly, start by learning how Excel writes code by recording our actions.

  • Break Big Problems into Small Steps: Try writing one comparatively small macro at a time.

  • Practice, Practice, Practice: The more we practice VBA, certainly the more natural it feels.

  • Ask for Help: Also leverage the massive Excel community online on forums, YouTube tutorials, and blogs.

  • Backup Your Work: Don not forget to save a copy before running new macros.

Conclusion on VBA Macros in Excel

Our professional life can be completely changed by learning how to use VBA macros in Excel to automate Excel tasks. Imagine being able to sip our coffee while Excel prepares our reports, sends our emails, cleans our data, and even organizes our files by simply pressing a button.

We can begin by recording basic macros. After that, we can proceed to write a few lines of VBA code. And before we know it, we’ll be creating strong automation that will save us hours each week.

Recall that yesterday was the ideal day to begin learning macros. Hence, today is the next best time.🎯

Also check this article on When to use VBA Macros and why

After that, do check out our articles on Excel and SQL –

  1. SQL Window Functions – Complete Guide for Beginners
  2. Pivot Table : Step by Step Guide for Beginners
  3. Top 50 Excel Formulas Every Analyst Must Know
Author: admin

8 thoughts on “How to Automate Tasks in Excel with VBA Macros (Beginner’s Guide)”

  7. Pingback: Data Cleaning in Excel: Best Techniques for Beginners - CodeAndExcel
  8. Pingback: Excel Charts and Data Visualization – A Complete Guide - CodeAndExcel

Leave a Reply

Your email address will not be published. Required fields are marked *