offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

8 Excel Macro Examples to Help You Master Macros

Microsoft Excel has been of great help to many of us for a very long time. It has helped us to do our daily tasks in a much easier and simpler way. It has helped people keep accounts, manage expenses, manage inventory, and numerous other things as well. Excel has several advanced features too, and we just need a little guidance to master them. One of these features is of creating macros.

In our daily life, we do a lot of things which are repetitive. We do tasks regularly and periodically and it would be extremely helpful if some of these repetitive acts could be automated. To do this in Excel, we use macros.

A macro is like an algorithm or a set of actions that we can use or run multiple times. A macro helps in automating or repeating tasks by recording or storing our input sequences like mouse strokes or keyboard presses. Once this input is stored, it makes up a macro which is open to any possible changes. Thus, we can allow Excel to study our input sequences and then imitate them in order to generate macros which automate repetitive tasks for us. Due to this outstanding feature, understanding how to deal with macros is really important.

Here, we will see some examples in order to learn and understand macros perfectly.

Example 1: How to Protect All the Worksheets in a Workbook All at Once

Macros can help us protect our worksheets with much less effort because all worksheets of a particular book can be protected at once. A macro will help us do this with a password which can be assigned in the macro itself, and the same password will help us unlock the sheets too.

Here is a sample code:

‘This code will secure all worksheets at once
Sub SecureEverySheet()
Dim ws As Worksheet
Dim passcode As String
passcode = “Pass000” ‘replace Pass000 with the code or password that you want to keep
For Each ws In Worksheets
      ws.Protect passcode:=passcode
Next ws
End Sub

Example 2: How to Save a Worksheet as a PDF

As you may know, we can make several worksheets inside each workbook. And there are times we might want to store a worksheet as a PDF, or each worksheet from the workbook as a different or separate PDF—so here again is macros to the rescue! This is a tedious process if done individually and manually, but a code can help us achieve the same result in much less time.

Here is the code:

‘This will store each worksheet of the workbook as a different PDF
Sub StoreWorksheetToPDF()
Dim ws As Worksheet
For Each ws In Worksheets
ws.ExportAsFixedFormat xlTypePDF, “C:UsersRishiDesktopMyfile” & ws.Name & “.pdf”
Next ws
End Sub

In the above code, the worksheet will be saved as a PDF in the folder called ‘Myfile’ on the desktop.

Example 3: How to Highlight the Duplicate Values from a Particular Selection

In a selection of cells, we might want to match and check for duplicate values. Finding duplicate values manually is time consuming and using a macro will speed up the process.

Here is the code we can use. Note that we can even change the color of the duplicated portions along with finding them:

‘This code will highlight the duplicate portions of a selection
Sub HilightDuplicateValue()
Dim MyR As Range
Dim MyC As Range
Set MyR = Selection
For Each MyC In MyR
If WorksheetFunction.CountIf(MyR, MyC.Value) > 1 Then
MyC.Interior.ColorIndex = 36
End If
Next MyC
End Sub

Example 4: How to Insert Multiple Worksheets in a Workbook in One Step

Adding worksheets manually to a workbook is tedious. Doing it with a code is much easier, and it allows us to insert any number of worksheets in a particular workbook all at once.

Here is the code:

‘This code will help in inserting a desired number of sheets in a workbook
Sub InsertManySheets()
Dim In As Integer
in = InputBox(“Enter the desired number of sheets that you want to insert”, “Enter Many Sheets”)
Sheets.Add After:=ActiveSheet, Count:=in
End Sub

Example 5: How to Delete All Blank Worksheets?

A workbook might have sheets which have been left blank and are of no use to us. For making the workbook more presentable it is advisable to remove the blank sheets present in the workbook.

Here is a code for removing any blank sheets:

‘This code helps in removing any blank sheets that might be present in the workbook
Sub RemoveBlankSheets()
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating= False
Application.DisplayAlerts= False
For Each ws In Application.Worksheets
If Application.WorksheetFunction.CountA(ws.UsedRange) = 0 Then
ws.Delete
End If
Next
Application.ScreenUpdating= True
Application.DisplayAlerts= True
End Sub

Example 6: How to Store a Backup of an Existing Workbook

Whenever we are working on something important, we want to make a backup of our data just to be on the safe side. Creating a backup helps secure the data. Using macros, we can save a backup of an existing workbook in the same directory where our original workbook is stored. The current date is also added to the backup file while it is being stored.

Here is the code:

‘This will help you in storing backup of an existing workbook
Sub WorkBackUp()
ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & _
“” & Format(Date, “mm-dd-yy”) & ” ” & _
ThisWorkbook.name
End Sub

Example 7: How to Highlight the Cells That Have Comments

Sometimes we just need to identify the comments and segregate them from the rest of the cells, so using a macro can easily serve our purpose.

Here is the simple code:

‘This is going to highlight and show us the cells that contain comments`
Sub HiLightCellsHavingComments()
ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbRed
End Sub

This code highlights the cells having comments in red. We can put in the color of our choice in place of ‘vbRed’ in order to change the color of the highlights.

Example 8: How to Count the Number of Workbooks That Are Currently Open but Are Unsaved

A macro can be written to identify the number of books which are open but not saved. We can be working with 7 or 8 workbooks at a time but some of them might be saved and others might not. Thus, before closing all manually and checking again and again, it is better to write a macro for seeing how many of them are unsaved.

Here is a code to achieve this:

‘This is going to count and tell us the number of workbooks which are currently open but not saved`
Sub Currentbooks()
Dim Wb As Workbook
Dim in As Integer
For Each book In Workbooks
If Wb.Saved = False Then
in = in + 1
End If
Next Wb
MsgBox in
End Sub

Once you know the number of books which are open and unsaved, you can find them and save them to prevent loss of work.

Microsoft Excel with its numerous features can be extremely helpful to users. The excellent feature of macros increases efficiency and saves a lot of time. It is because of such features that Excel has become mandatory in almost all professional walks of life. For some people writing code was only associated with various programming languages; but to the contrary, Excel opens up a whole new world with codes of macros. With a little time and effort, you can definitely learn how to create macros to get the most out of Excel. Go ahead and learn these amazing advanced features of Microsoft Excel. All the best!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!