25 VBA Macro Example for Enhanced Productivity

Get FREE Advanced Excel Exercises with Solutions!

When it comes to boosting output or gaining time at work, macros are your best friend. Using the VBA coding language, we can automate anything from minor to large processes. We are aware that you may have occasionally considered some of Excel’s limits, but thanks to the VBA code, you can do away with them altogether. In this article, we will provide some handy VBA macro example codes in Excel.

Whether you are a beginner or an experienced Excel user, we hope that this reference will provide you with valuable insights into the world of VBA macros and how they can be used to improve your productivity and efficiency.


Table of Contents Expand

How to Launch and Insert Code in Visual Basic Editor in Excel?

The Developer tab contains the VBA applications including creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point in the world of VBA; just follow along.
📝 Note: By default, the Developer tab remains hidden. You can learn how to display the Developer tab by following this linked article.

  • Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.

Moving to the developer tab and clicking on the visual basic button

It launches the Microsoft Visual Basic for Applications window.

  • Now, click the Insert tab and choose Module from the list. Afterward, we get a small Module window to insert our VBA code.

Inserting module in the visual basic for applications window to create VBA Macro example

The advantage of inserting the code in the Module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can make our code only available for a specific worksheet.


How to Run Macro in Excel?

After creating a code, the most important task is to run it for a specific task. We can run our code of the module in two different ways. The first one is:

  • After completing the code script, click the green-colored play button to Run the code. You can press F5 on the keyboard to do the same task.

Executing code with run button

Another way is to execute it from the Macro dialog box. To do this, follow these simple steps.

  • At first, click on the Developer tab and then select Macros in the Code group of commands on the ribbon.

clicking on Macros option on Developer tab

  • Instantly, a Macro dialog box will pop up. You can see multiple Macro names available which you have created for this workbook. Simply, select one and click on the Run button.

run code from Macro dialog box

That’s how you can easily initiate the code.


25 Useful VBA Macro Example in Excel

To move on to the next step, we will use the below dataset to perform the exercises. This is the “Tech gadgets Sales Summary” of a particular shop. This dataset includes the “SL. No”, “Product Name”, “Units”, “Unit Price”, and “Total Price” under columns B, C, D, E, and F respectively.

Dataset of sales summary

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll utilize this dataset to create and show 25 fundamental VBA macro examples in Excel. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.


Example 1: Adding Serial Number

If you work with a lot of data, this example will enable you to automatically add serial numbers to your Excel spreadsheet. Also, you can do it by using the Fill Handle. But this process is more efficient and less time-consuming for a bigger dataset.
Here, we have a blank Column B. We have to fill up these cells with the serial number.

dataset without serial number

You can use the below code to complete this task.

Sub Add_Serial_Numbers()
'Declaring variable
Dim x As Integer
'Using For loop
For x = 5 To 14
    Cells(x, 2).Value = x - 4
Next x
End Sub

The objective of this macro is to adjoin serial numbers in Column B of an Excel sheet. The macro utilizes a loop to cycle over the rows from Row 5 to Row 14 (where x denotes the row number), deducting 4 from each row’s row number and assigning the result to the cell in Column B that corresponds to that row.

VBA code to add serial number

After running this macro, it will add serial numbers in the spreadsheet from 1 to 10 in the B5:B14 range.

serial number added through VBA macro

Read More: Types of VBA Macros in Excel


Example 2: Converting All Formulas into Values

If you want to know the conversion from the formulas to values, this example could be of help. Here, you will find all the formulas in Column F.

total prices are in formula format

You can use the following code.

Sub Convert_Formulas_To_Values()
'Specifying the formula range cells
With ActiveSheet.UsedRange
    'Converting the formulas to values
    .Value = .Value
End With
End Sub

Code Breakdown

  • This code begins with a Sub statement that defines the name of the macro, which in this case is “Convert_Formulas_To_Values“.
  • The With statement is used to specify the range of cells that contain formulas in the active sheet. The UsedRange property of the ActiveSheet object is used to get the range of cells that contain data in the sheet.
  • Inside the With statement, the .Value property is used twice to convert the formulas in the range to their resulting values. This is accomplished by setting the value of the cell to its current value.
  • Finally, the End With statement is used to end the With block.

VBA code to convert formulas to value

When this code is run, it converts all the formulas in the active sheet to their resulting values.

formulas converted into values

This can be useful when you want to preserve the results of a calculation or analysis, but no longer need the original formulas.
Note: This action cannot be undone, so it’s a good idea to make a backup copy of your worksheet before running this macro.

Read More: Excel Macro Shortcut Key


Example 3: Auto-fitting Columns and Rows

Here, we’ll show how we can resize column widths and row heights automatically. It’s significant for the attractiveness of the sheet.
Sometimes, we have column width which isn’t essential to display the full-length value. As a result, it becomes very unsightly for us. See the image below to understand the problem better.

improper column width

The same thing goes for improper row heights.

improper row heights

Firstly, we’ll discuss how we can solve the column width resizing issue automatically so that all columns get auto-fitted to their content length. We have to just apply a simple VBA code to solve this problem.

The following is the code for this example.

Sub Auto_Fit_Columns()
Cells.Select
'Auto fit the entire column
Cells.EntireColumn.AutoFit
End Sub

VBA code to autofit columns

Just run the code and see the magic. All columns get auto-fitted in this particular sheet.

auto-fitted columns

Auto-fitting rows are similar to the previous task. We just have to replace the EntireColumn object with the EntireRow object in the code.

Sub Auto_Fit_Rows()
Cells.Select
'Auto fit the entire row
Cells.EntireRow.AutoFit
End Sub

VBA macro to autofit rows

And the result of executing this code is before our eyes.

auto-fitted rows


Example 4: Inserting Multiple Rows and Columns

Sometimes, we need to add new rows to give new entries in our worksheet. On the other hand, if we want to add new fields inside our data range, we have to add new columns. To tackle these problems, we can use two simple VBA macros in our Excel file.
First and foremost, we will show how to insert rows in the sheet.

Below is the code for this example.

Sub Insert_Multiple_Rows()
On Error GoTo Handler
' Get the number of rows to insert from the user
Dim row_num As Integer
row_num = InputBox("Enter the number of rows to insert:", "Insert Rows")
' Insert the rows below the active cell
ActiveCell.Offset(1).Resize(row_num).EntireRow.Insert
Handler: Exit Sub
End Sub

ActiveCell.Offset(1).Resize(row_num).EntireRow.Insert in this VBA code is to insert a certain number of rows beneath the active (selected) cell in an Excel worksheet. Here’s how this line of code works:

  • ActiveCell refers to the presently selected cell in the worksheet.
  • Offset(1) moves the active cell down one row. This is so that the new rows can be inserted below the current cell rather than above it.
  • Resize(row_num) changes the selection’s size to match the number of rows indicated by the row_num variable, which corresponds to the number of rows the user supplied in the input box earlier in the code.
  • EntireRow selects the full resized row or rows.
  • Last but not least, Insert adds a new row or rows to the worksheet beneath the active cell.

VBA macro example to insert multiple rows

Before executing the code, make sure to select the cell under which you want to add multiple rows. Let’s say we want to add rows below the row of SL. No. 3. So, we selected cell B7 and then run the code.
As soon as the code runs, an input box pops up to get the number of rows to be inserted below the selected cell. Here, input your preferred number to get new rows of this count.

  • In this case, we inserted 2. Then, click OK.

entering number of rows to insert in the input box

Instantly, it adds two rows beneath Row 7.

2 new rows added in sheet

For inserting multiple columns in our dataset, we’ll use almost identical code. The code is the following.

Sub Insert_Multiple_Columns()
' Get the number of columns to insert from the user
Dim col_num As Integer
col_num = InputBox("Enter the number of columns to insert:", "Insert Columns")
' Insert the columns to the right of the active cell
ActiveCell.Offset(0, 1).Resize(, col_num).EntireColumn.Insert
End Sub

VBA macro example to insert multiple columns

Before initiating the code, we selected cell D4, as we want to insert columns after Column D. Then run the code.

  • Again, enter your desired integer number and click OK.

entering number of columns to insert in the input box

The following image is resemblance to the final output.

new column inserted inside dataset


Example 5: Inserting a Row After Every Other Row

In this example, we’ll demonstrate how you can insert a row after every other row in your dataset. Copy and paste this code into your code module.

Sub Insert_Row_After_Every_Other_Row()
'Declaring Variables
Dim Xrng As Range
Dim row_count As Integer
Dim j As Integer
Set Xrng = Selection
'Counting rows
row_count = Xrng.EntireRow.Count
'using For loop
For j = 1 To row_count
    'Inserting row after every other row
    ActiveCell.EntireRow.Insert
    ActiveCell.Offset(2, 0).Select
Next j
End Sub

VBA macro to insert row after every other row

Just make sure to select the range before executing the code. Here, we selected the B6:F14 range in our dataset. The reason for starting from Row 6 is that Excel inserts the row above the selected cell by default.
The result is as follows.

new rows added between two rows


Example 6: Hiding and Unhiding Rows and Columns

First, we’ll hide rows and columns separately. It’s an easy task.

For hiding rows, the code is the following.

Sub Hide_Rows_Columns()
' Get the range selected by the user
Dim rng As Range
Set rng = Selection
' Hide the rows and columns in the selected range
rng.Rows.Hidden = True
'rng.Columns.Hidden = True
End Sub

Here, you can see that we have kept a line of code in the comment form. Because we’ll use this part to hide columns.

VBA macro to hide rows

Just select the row or rows which you want to hide and then run the code. For example, we want to hide rows 7 and 8. So, we selected them and ran the code. As a result, those rows got hidden.

rows 7, 8 get hidden

Similarly, we can hide the columns that we prefer. Simply, we have to uncomment a line from the previous code.

Sub Hide_Rows_Columns()
' Get the range selected by the user
Dim rng As Range
Set rng = Selection
' Hide the rows and columns in the selected range
'rng.Rows.Hidden = True
rng.Columns.Hidden = True
End Sub

You can see columns D and E aren’t visible also.

columns D, E get hidden

Now, we’ll unhide these hidden rows and columns with just one click. Here is the code for this purpose.

Sub Unhide_Rows_Columns()
'Unhide columns
Columns.EntireColumn.Hidden = False
'Unhide rows
Rows.EntireRow.Hidden = False
End Sub

unhiding rows and columns at a time

The resulting output is as follows. The entire dataset gets back its initial presence.

dataset is visible with all rows and columns


Example 7: Finding Last Used Row in an Excel Worksheet

Here, we’ll find the last used row in the active worksheet and show this row number through a message box.
Row 14 is the last used row in our case.

dataset with multiple rows upto row 14

The following code is the master key to do that.

Sub Last_Used_Row()
Dim LUR As Integer
LUR = Cells(Rows.Count, 2).End(xlUp).Row
MsgBox "The last used row in this sheet is ROW " & LUR
End Sub
  • We used the Cells function to select the cell in the last row of Column B (Rows.Count returns the total number of rows in the worksheet, and End(xlUp) moves up from the bottom of the sheet to the last cell with a value).
  • The Row property returns the row number of the selected cell.

VBA macro to find last used row in active sheet

Simply, you get a message box with the message containing the last used row number after running the code.

message box showing the last used row number


Example 8: Changing Case of Characters

To exemplify, we’ll demonstrate how to change the case of characters in Excel with VBA macro. In this case, we’ll take the column of “Product Name” into consideration. Firstly, we’ll convert them all to upper case, and after that to proper case. And, in the last, to lowercase.
This macro code works for converting all the text values to uppercase.

Sub Convert_Upper_Case()
'Declaring variable
Dim Xrng As Range
'Using For Each loop
For Each Xrng In Selection.Cells
    'Using If Statement
    If Xrng.HasFormula = False Then
        'Specify the Range
        Xrng.Value = UCase(Xrng.Value)
    End If
Next Xrng
End Sub

Here, we used Xrng.HasFormula = False in the If statement to omit values that got formula. If the value isn’t the output of a formula, then we changed its case only. We used the UCase function to achieve the main part.

VBA code to convert all characters to upper case

Make sure to select the range before executing the code. We selected cells in the C5:C14 range. Therefore, the final results show all the uppercase text in the below image.

all texts are uppercase in column C

Now, we’ll take this output as our new input and change them all to the proper case. In the proper case, the first letter of each word gets written in capital letters, and the remaining in small letters.

Here’s the working code to do this.

Sub Convert_Proper_Case()
'Declaring variable
Dim rng As Range
'Using For Each loop
For Each rng In Selection
    'Reading the worksheet text range cells
    If WorksheetFunction.IsText(rng) Then
        'Converting all the selected range to proper case
        rng.Value = WorksheetFunction.Proper(rng.Value)
    End If
Next rng
End Sub

VBA code to convert characters to proper case

Don’t forget to select the range before initiating the code. The final looks like the image below. You can understand its functionality better in the highlighted texts.

all texts are proper case in column C

At this time, we’ll convert all the texts in Column C to lowercase characters. Use this code to do this.

Sub Convert_Lower_Case()
'Declaring variable
Dim Xrng As Range
'Using For Each loop
For Each Xrng In Selection.Cells
     'Using If Statement
    If Xrng.HasFormula = False Then
       'Specify the Range
        Xrng.Value = LCase(Xrng.Value)
   End If
Next Xrng
End Sub

VBA macro to convert characters to lower case

Select the range before you run the code and the final results are displayed in the graphic below, which only has lowercase text.

all texts converted to lower case


Example 9: Highlighting Multiple Blank Cells

While you can highlight blank cells utilizing conditional formatting or the go-to Special dialog box, it’s easier to use a macro if you need to do so frequently. Once built, you can save this macro in your own macro workbook or add it to the Quick Access Toolbar. VBA Macro example could be the way to get it in a faster approach.
If you look carefully, you can see multiple blank cells in our worksheet.

multiple blanks cells in worksheet

The VBA code to do that is the following.

Sub Highlight_Blank_Cells()
'Declaring variable
Dim MyData As Range
Set MyData = Selection
'Highligting the selected data range with color
MyData.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbGreen
End Sub

VBA code to highlight blank cells in excel

Select the data range and run the macro. And after that, it will show you the below image with highlighted blank cells in your worksheet.

blank cells get highlighted through VBA macro example


Example 10: Unmerging All Cells in Active Sheet

Occasionally, we have merged cells in our worksheet. There could be any reason to unmerge them. Merged cells cause problems in the time of copy and pasting. Let’s leave this and come to the real point. How can we get rid of these merged cells? So easy. Just unmerge them. In the following image, there are some merged cells highlighted with a different background fill color.

merged cells present in dataset

Let’s unmerge those cells. Watch the following code to do this.

Sub UnmergeCells()
' Declare variables
|Dim mergedCell As Range
Dim cell As Range
' Loop through each merged cell in the active sheet
For Each mergedCell In ActiveSheet.UsedRange.Cells
    If mergedCell.MergeCells Then
        ' Copy the merged value to the unmerged cells
        mergedCell.MergeCells = False
        If IsError(mergedCell.MergeArea.Value) Then
            ' Skip over cells that contain errors
        Else
            mergedCell.Value = mergedCell.MergeArea.Value
        End If
    End If
Next mergedCell
End Sub

Code Breakdown

  • The first line declares the sub-procedure “UnmergeCells“.
  • The next four lines declare two variables, “mergedCell” and “cell“, as Ranges. These variables will be used to store the merged cells and individual cells, respectively.
  • Then, the “For Each” loop starts and iterates through each cell within the “UsedRange” of the active sheet.
  • The “If” statement checks whether the current cell is merged. If it is merged, then the code continues to execute the following lines. If it is not merged, then it skips to the next cell.
  • The “mergedCell.MergeCells = False” line unmerges the cell by setting the “MergeCells” property to False.
  • The “If IsError(mergedCell.MergeArea.Value) Then” line checks if the merged cell contains an error. If it does, then the code skips to the next cell.
  • After that, the “mergedCell.Value = mergedCell.MergeArea.Value” line copies the value of the merged cell to the unmerged cell.
  • The loop continues to iterate through each cell in the UsedRange until all merged cells have been unmerged and their values have been copied to the unmerged cells.
  • The sub-procedure is closed with the “End Sub” statement.

VBA macro to unmerge cells

Now the values in those cells are not center aligned. They got top-aligned as they are not merged anymore. And if you select one of them, you can see the Merge & Center command isn’t highlighted as they got unmerged now.

all cells get unmerged


Example 11: Highlighting Cells with Misspelled Words in Excel

Spell checking is not available in Excel like it is in Word or PowerPoint. We can see there are some misspelled words in our dataset. We’ve marked them with red rectangular boxes.

cells with misspelled words

Therefore, you can use this code to instantaneously highlight every cell that contains a spelling error.

Sub Check_Spelling_Error()
'Declaring variable
Dim MyCheck As Range
'Using For Each loop
For Each MyCheck In ActiveSheet.UsedRange
     'Using this statement to check spelling mistake
    If Not Application.CheckSpelling(Word:=MyCheck.Text) Then
        'Coloring the misspelled words
        MyCheck.Interior.Color = vbRed
        MyCheck.Font.Color = vbWhite
    End If
Next MyCheck
End Sub

In this code, “If Not Application.CheckSpelling(Word:=MyCheck.Text) Then”, this line uses the Application.CheckSpelling method to check for spelling errors in the text of the current cell (MyCheck.Text). If the CheckSpelling method returns False, which indicates a spelling error, then the code inside the If statement will be executed.

MyCheck.Interior.Color = vbRed
MyCheck.Font.Color = vbWhite

This code sets the interior color of the current cell to red (vbRed) and the font color to white (vbWhite) to highlight the misspelled word.

VBA code to check spelling error and highlight those cells

Consequently, each highlighted misspelled word you have will show up in the below image after running the above macro.

cells with misspelled words get highlighted


Example 12: Printing Sheet Name

The active sheet will receive all the sheet names by this macro code. When you run this code, it will copy the name of all of the accessible sheets to the current sheet. The present condition of our sheet is like the following.

showing sheet names at sheet tabs

After initiating the code, it will get filled up with the sheet names in Column B. Following is the working code for this example.

Sub Print_Sheet_Names()
'Declaring variable
Dim j As Integer
'Using For loop to count the number of sheets
For j = 1 To Sheets.Count
     'Print all the sheets available in the workbook
    Cells(j + 3, 2).Value = Sheets(j).Name
    With Cells(j + 3, 2).Borders
            .LineStyle = xlContinuous
           .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
Next j
End Sub

This VBA code copies the names of all the sheets available in the workbook. It uses a For loop to iterate through each sheet and prints its name in a specific cell using the Cells method. Additionally, it also applies a border to the cell containing the sheet name.

VBA macro to print sheet names in cells

Finally, it gets all the names of the worksheet after running the code.

all remaining sheet names are printed in the worksheet


Example 13: Sorting Worksheets Alphabetically

Normally worksheets are arranged according to the period of their creation. But we can sort them alphabetically if we wish so. That means if we had 3 worksheets named B, C, and A (sorted according to creation period), they would sort themselves and will appear in the sequence A, B, and C before us.
In the below image here, all the sheet names are not in order alphabetically.

sheets are not in alphabetical order

This macro code might be quite helpful if you have a workbook with lots of worksheets and you want to organize them alphabetically.

Sub Sort_Worksheets_Alphabetically()
'Fast the code running time
Application.ScreenUpdating = False
'Declaring variables
Dim MySheetCount As Integer, x As Integer, y As Integer
MySheetCount = Sheets.Count
'Using nested For loop to sort the worksheets alphabetically
For x = 1 To MySheetCount - 1
    For y = x + 1 To MySheetCount
        If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then
            Sheets(y).Move before:=Sheets(x)
        End If
    Next y
Next x
Application.ScreenUpdating = True
End Sub

VBA script to sort sheets alphabetically

After running the above macro, you will all the sheet names in order alphabetically.

sheets are sorted alphabetically

Now they are sorted according to their name, not to their formation timeline.


Example 14: Inserting Multiple Worksheets in an Excel Workbook

If you wish to add several worksheets to your workbook at once, you can use this code. You will see an input box to enter the total number of sheets you want to enter when you run this macro code. It is one of the time-saving VBA Macro examples. The code for this example is beneath.

Sub Add_Multiple_Sheets()
'Declaring variable
Dim SheetsNumber As Integer
'Put the number of sheets
SheetsNumber = InputBox("Enter number of sheets to insert", "Insert Sheets")
'Adding the additional sheets after the current active sheet
Sheets.Add After:=ActiveSheet, Count:=SheetsNumber
End Sub

This code simply adds multiple sheets after the active worksheet.

VBA macro to add multiple worksheets

Right away you run the code, an input box will open to get the number of worksheets to insert after the active sheet. In this case, we wrote 2 and clicked OK.

entering number of sheet to insert in input box

Immediately, it’ll add two blank sheets after the operating sheet.

2 blank sheets added after active sheet


Example 15: Unhiding Multiple Sheets

If a workbook you are using includes numerous sheets that are hidden, you must unhide each sheet one at a time. If there are numerous hidden sheets, this process could take some time. In the image below, there is one sheet visible in the workbook, all others are hidden.

all sheets are hidden without active sheet

This code will display every worksheet in the workbook.

Sub Unhide_Multiple_Sheets()
'Declaring variable
Dim hidden_sheet As Worksheet
'Using For Each loop
For Each hidden_sheet In ActiveWorkbook.Worksheets
     'All the sheets will be visible
     hidden_sheet.Visible = xlSheetVisible
Next hidden_sheet
End Sub

VBA macro to unhide all hidden sheets

So, after executing the code, all other sheets are visible/unhidden again.

all sheets are visible in sheet tabs


Example 16: Protecting and Unprotecting All Worksheets

This is the example you can use to protect all of your worksheets at once. This code will go over each worksheet individually and secure it. Also, you will get the code to unprotect them at once.

The VBA code to protect your worksheets is the following.

Sub Protect_Multiple_Sheets()
'Declaring variable
Dim MySheet As Worksheet
'Using For loop
For Each MySheet In Worksheets    
    'Protecting multiple sheets
    MySheet.Protect
Next MySheet
End Sub

VBA code to protect sheets

Now, most of the commands and features on the ribbon get greyed out. That means they are not available now. You cannot use them to edit, or change your worksheet.

you cannot edit anything while the sheet is protected

If you tried to enter any value or change any value, Excel will show a message box like the following.

warning message to unprotect the sheet

Now, if we want to unprotect sheets again, just use the code below.

Sub Unprotect_Multiple_Sheets()
'Declaring variable
Dim MySheet As Worksheet
'Using For loop
For Each MySheet In Worksheets
    'Unprotecting multiple sheets
    MySheet.Unprotect
Next MySheet
End Sub

You can notice that this code is almost similar to the previous code. We just changed the . Protect method to .Unprotect method.

VBA macro to unprotect the sheet again

Again, we become able to edit anything on the worksheet.

editing is now possible


Example 17: Exporting Individual Worksheets as PDF Files

PDF files are easy to share, can be opened on most devices, and preserve the formatting of the original document. Exporting individual worksheets as PDF files is a useful feature when you need to share specific sheets of a workbook with others who may not have access to the original Excel file.

Here’s the VBA code that exports individual worksheets in a workbook as PDF files.

Sub SaveWorksheetsAsPDFs()
Dim ws As Worksheet
Dim path As String
'Get the path to save the files to
path = Application.ActiveWorkbook.path & "\"
'Loop through each worksheet and save as PDF
For Each ws In Worksheets
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & _
    ws.Name & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
Next ws
End Sub

The current worksheet is saved as a PDF file inside the loop using the Worksheet object’s ExportAsFixedFormat method. There are numerous parameters that must be given for the method:

  • Type: This describes the file format; for PDF files, it is “xlTypePDF” in this example.
  • Filename: This gives the name and location of the finished PDF file. The name of the worksheet and the “.pdf” extension are combined with the path variable to form the whole filename.
  • Quality: The PDF quality option, in this case “xlQualityStandard,” is specified by this.
  • IncludeDocProperties: In this code, it is set to True, indicating that document properties should be included in the output file.
  • IgnorePrintAreas: This specifies whether to ignore print areas and print the entire worksheet, which is set to False in this code.
  • OpenAfterPublish: This specifies whether to open the saved PDF file after it’s created, which is set to False in this code.

VBA macro script to save worksheets as separate pdf files

Each worksheet in the active workbook is saved as a separate PDF file in the designated file location when this code is executed. When you need to send individual worksheets from a workbook to other people as PDF files, this code may be very much helpful.


Example 18: Adding Timestamp to Workbook Name While Saving

Adding time automatically to the filename is a good way to easily know when the last work was done. We can do that through VBA macro. This code will save a copy of the main workbook with a timestamp in its name.

Sub SaveWorkbook_With_Timestamp()
Dim ts As String
Dim wbName As String
'Get the current timestamp
ts = Format(Now(), "yyyy-mm-dd_hh-mm-ss")
'Get the workbook name and path
wbName = ThisWorkbook.Name
'Add the timestamp to the workbook name
wbName = Left(wbName, InStrRev(wbName, ".") - 1) & "_" & ts & ".xlsm"
'Save the workbook with the new name
ThisWorkbook.SaveAs wbName
End Sub

Here, “wbName = Left(wbName, InStrRev(wbName, “.”) – 1) & “_” & ts & “.xlsm
This line uses the Left() function to get all characters of the wbName variable from the beginning to the position of the last dot (.) minus 1, which removes the file extension. Then, it appends an underscore, the timestamp (ts variable), and the file extension “.xlsm” to the wbName variable, creating a new name with the timestamp appended.

VBA code to add timestamp in name while saving file

Note: This will save the workbook as a macro-enabled workbook (.xlsm) since the original workbook likely has VBA code in it. If the original workbook is not macro-enabled, you may need to change the file format to the appropriate type (e.g., .xlsx) in the SaveAs method. And that’s it! The workbook will be saved with the timestamp appended to its name, allowing you to create unique copies of the workbook with different timestamps for version control or tracking purposes.
You can see the output. A new copy of this file is saved with the perfect timestamp in its name and also in the same folder of the source workbook.

blank worksheet in the workbook


Example 19: Erasing Blank Worksheets

As you can see we have a blank worksheet named “Erase Blank Worksheet” in our workbook. It’s just beside the worksheet “Highlight Cells Misspelled Word”.

new copy of file is save with timestamp in its name

This macro will erase all the blank worksheets from the workbook we are using while running the following macro code.

Sub Erasing_Blank_Sheets()
'Declaring variable
Dim MySheet As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Using For Each loop
For Each MySheet In ActiveWorkbook.Worksheets
    'using this statement to Count blank sheets
    If WorksheetFunction.CountA(MySheet.UsedRange) = 0 Then
        'Deleting blank sheets
        MySheet.Delete
    End If
Next MySheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

VBA macro to erase blank worksheets in excel

Now, we can execute the code. As a result, there is no blank sheet in the workbook after running the above macro code.

no blank worksheets are available now

You can observe the blank sheet named “Erase Blank Worksheet” isn’t available now. It gets deleted with our macro.


Example 20: Inserting Different Color Code Index

In this example, we will insert numbers from 1 to 10 and their respective color index in the next column. We have the worksheet in this state now.

columns to insert index numbers and respective colors

In Column B, we’ll enter numbers and in the right next column, we’ll insert their respective color index. The following VBA code will do the rest of the work.

Sub Insert_Multple_Colors()
'Using variable
Dim j As Integer
'Using For loop
For j = 1 To 10
    Cells(j + 4, 2).Value = j
    'Inserting the color  code index
    Cells(j + 4, 3).Interior.ColorIndex = j
Next
End Sub

Code Breakdown

  • The For loop will repeat 10 times, with the variable “j” being set to each value between 1 and 10.
  • Cells(j + 4, 2).Value = j: This line sets the value of the cell in column 2 (B) and row “j + 4” to the value of “j“. This means that the first number will be inserted into cell B5, the second into B6, and so on.
  • Cells(j + 4, 3).Interior.ColorIndex = j: This line sets the color of the cell in column 3 (C) and row “j + 4” to the color with index number “j“.

VBA macro to insert index number and corresponding colors in excel

Finally, you will observe the following output in the image with different color code indexes.

index numbers and colors inserted in worksheet

Excel has 56 built-in colors with corresponding index numbers, and this code uses the index number to set the background color of the cell.


Example 21: Sorting Data by Column

In the following image, we can see that our data isn’t arranged properly. Their serial numbers aren’t arranged properly but rather in a random serial.

unsorted data

We’ll sort this dataset based on the serial numbers in Column B. We need a short code for doing this. The code is given below.

Sub Sort_Data_by_Column()
Range("B4:F14").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes
End Sub

The Sort method takes three arguments:

  • Key1: This specifies the column by which the data is sorted. In this case, the sort key is set to the range “B4“, which means that the data is sorted based on the values in Column B.
  • Order1: This specifies the sort order. In this case, the sort order is set to “xlAscending“, which means that the data is sorted in ascending order (e.g 0, 1, 2, 3,…, 10).
  • Header: This specifies whether the range has a header row that should not be sorted. In this case, the header is set to “xlYes“, which means that the first row of the range (“B4:F4“) is treated as a header row and is not sorted with the data.

VBA macro to sort data by column

Run the code and look they are in perfect order now.

data sorted by column B


Example 22: Highlighting Top 3 Values

In this example, we opt to highlight the top 3 values in the “Total Price” column with a different background color. How can we do that with VBA?

This simple code could be the solution for you.

Sub Highlight_Top3()
Dim rng As Range
Dim cell As Range
Dim cnt As Integer
Dim max_Val As Variant
'Get the range of the Total Price column
Set rng = Range("F5:F14")
'Loop through the range and find the top 3 values
For cnt = 1 To 3
    max_Val = Application.WorksheetFunction.Large(rng, cnt)
    For Each cell In rng
        If cell.Value = max_Val And cell.Interior.ColorIndex = xlNone Then
            cell.Interior.Color = RGB(255, 255, 0)
            Exit For
        End If
    Next cell
Next cnt
End Sub

Code Breakdown

  • The For loop is used to loop through the range and find the top 3 values. The loop starts at 1 and ends at 3, meaning it will run 3 times to find the top 3 values.
  • The max_Val variable is set to the cnt-th largest value in the range using the Large function. The Large function returns the cnt-th largest value in the range.
  • The inner For Each loop is used to loop through each cell in the range.
  • The If statement is used to check if the value in the current cell is equal to the max_Val and if the cell is not already highlighted. If both conditions are true, the Interior.Color property of the cell is set to yellow using the RGB function. The RGB function returns a color value based on the red, green, and blue components passed as arguments.

VBA macro to highlight top 3 values in Excel

You can see the result here.

top 3 values are highlighted in column F


Example 23: Applying Paste Special

We do have the Paste Special VBA Macro example to copy data and paste it on another cell range, just like a worksheet. It enables us to paste the data exactly as it is, just the formulas, just the values, or all three in the same way.

The code is the following.

Sub Paste_Special()
'Copy the specified range of data set
Range("B4:F14").Copy
'Paste in the specified range
Range("H4").PasteSpecial xlPasteAll
Cells.EntireColumn.AutoFit
Application.CutCopyMode = False
End Sub

This code copies the B4:F14 range and pastes it in the H4 range using the Paste Special method.

VBA code to apply paste special method

Finally, you will see the same data set on the right side of the worksheet.

same datarange copied through paste special method in VBA


Example 24: Refreshing Pivot Tables in Workbook

Look, we have created a PivotTable from the data in the B4:F10 range. If you don’t know how to create a PivotTable in Excel, you can follow the linked article.

pivot table created from dataset

Then, if we change the D7 cell value from 85 to 90, you will see that the total price in the F7 cell is updated below according to the respective changing value. But, in the PivotTable, no change occurs.

no change in pivot table though we changed the value in dataset

The following code will help you to update your pivot table easily.

Sub Refresh_PivotTables()
'Declaring variable
Dim MyTable As PivotTable
'Using For loop
For Each MyTable In ActiveSheet.PivotTables
    'Refreshing the table
    MyTable.RefreshTable
Next MyTable
End Sub

If your workbook contains multiple pivot tables, you can use this code to update them all at once.

VBA code to refresh pivot table automatically in whole workbook

After running the above macro code, it will show the updated value in the PivotTable according to our corresponding dataset.

pivot table is updated through VBA code


Example 25: Creating User-Defined Function

Microsoft provides us with a variety of built-in capabilities to make the VBA Macro example perform more quickly. However, we can program our own functions using VBA, often referred to as “User Defined Functions” (UDF). In Excel VBA, they are also referred to as “custom functions.”

To calculate the total price, we can build a custom function of our own which will take the unit and unit price as arguments. The code is given below.

Function Total_Price(x As Long, y As Long) As Long
'Using Product formula
Total_Price = x * y
End Function

creating user-defined function with VBA code

You can write this custom function in the worksheet like the other functions we use in Excel. As we are showing macro-related examples, we prefer to use this function in another VBA macro to take it into action.

We can use the following VBA macro in this case.

Sub Calculate_Total_Price()
Dim LRow As Long
Dim i As Long
'Get the last row of data in column B
LRow = Cells(Rows.Count, 2).End(xlUp).Row
'Loop through each row of data
For i = 5 To LRow 'Starting from row 5 because row 1 to 4 are header rows
    'Calculate the total price using the _
    Total_Price function and insert the value in column F
    Cells(i, 6).Value = Total_Price(Cells(i, 4).Value, Cells(i, 5).Value)
Next i
End Sub

VBA macro to calculate value using user-defined function

After executing the code, we can get the total prices in Column F in our sheet. Clicking on the cells in the F5:F14 range will show that no formula or function is showing in the cell. Because we added value directly through the VBA code.

total prices are calculated through VBA code


How to Enable Macro in Excel?

Before starting this section, remember that enabling macros can increase the security risks in your spreadsheet, so it’s important to only enable macros from trusted sources.
Because of security issues, Microsoft has blocked VBA macros by default. To use the macro, you have to enable it first.
Clicking the Enable Content button on the yellow security notification bar that shows at the top of the sheet when you initially access a workbook with macros is the simplest and fastest way to enable macros for a single workbook.

enabling macro in workbook


How to Change Macro Setting from Trust Center?

Also, you can change the macro setting that allows you to open any macro-enabled file without any security warning. To do this, you have to change the setting from the Trust Center. Just follow the steps below:

  • Firstly, click on File >> Options.

options in file tab

  • Then, from the Excel Options dialog box, select Trust Center >> Trust Center Settings.

Excel options dialog box

  • Now, in the Trust Center wizard select Macro Settings >> choose Enable VBA macros option.
  • Lastly, click OK and you are ready to go.

Trust center dialog box

Now, you don’t need to enable content every time you open a new macro-enabled Excel workbook.


How to Test and Debug VBA Macro Code?

You can easily test and debug a macro using the F8 key on the keyboard. You can then see the impact of each line on your worksheet as you go through the macro code line by line. Yellow highlighting indicates the line that is presently being run.

test and debug VBA macro code

Simply, press the Reset button on the toolbar to get out of debug mode.

reset button to stop debugging


How to Save VBA Macro in Excel?

Generally, Excel files are saved in .xlsx format. But it’s different in the case of macro-enabled Excel files. They get the .xlsm extension after their name. To save it, follow the steps below.

  • At first, press CTRL + S keyboard shortcut command to save any file.
  • Then, in the Save As dialog box, choose Excel macro-Enabled Workbook format in the Save as type field and click on the Save button.

saving macro enabled xlsm file

Now the macro-enabled Excel file has been saved, and it can be opened and used with macros enabled.


How to Delete Macro from Excel?

You need to delete macros from your file for several reasons. If a workbook contains a macro that we no longer need or that is potentially harmful, it is a good practice to delete it to reduce the risk of accidental or malicious execution. See the following steps to delete macros from the workbook.

You can easily delete any macro from the Macro dialog box. We have already discussed how we can open the Macro dialog box.

  • Just select the macro you want to delete and click on the Delete button.

deleting macro from Macro dialog box

This is the easiest way to delete macros. You can do it in another way: using the Visual Basic Editor.

  • Firstly, right-click on the module name on Project Explorer.
  • Then, you can find the remove option in the context menu. Just click on it.

deleting macro from Project Explorer in VBE

Overall, deleting macros from a workbook can improve the security, file size, clarity, and efficiency of the workbook, especially if we don’t need the macros anymore or if they are potentially harmful.


Things to Remember

  • Always save a backup copy of your Excel file before making any changes to it with the VBA code.
  • When writing VBA code, use comments to explain what your code does. This will make it easier for you (and others) to understand the code in the future.
  • Use meaningful variable names to make your code easier to read and understand.
  • Be aware of the limitations of VBA macros. Some Excel features may not be accessible through VBA, and there may be performance issues with very large data sets.

Frequently Asked Questions

1. Can I record a macro in Excel?

Yes, you can use the macro recorder in Excel to record a series of actions and generate VBA code based on those actions. However, the resulting code may not be as efficient or customizable as handwritten code.

2. Can VBA macros be used in other Microsoft Office applications?

Yes, we can use VBA macros in other Microsoft Office applications, such as Word and PowerPoint. However, the specific VBA code required may differ depending on the application.

3. Are VBA macros secure?

VBA macros can pose security risks if they we don’t create or use them properly. Macros can potentially contain malicious code, so it’s important to enable macro security settings in Excel and only run macros from trusted sources.

4. What are some common uses for VBA macros in Excel?

We can use VBA macros for a variety of tasks in Excel, such as automating data entry, formatting reports, creating custom functions, and manipulating data. Some common examples include automating financial models, generating charts and graphs, and performing data analysis.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

To sum up, VBA macros are a strong tool that may assist Excel users in automating processes, manipulating data, and developing customized solutions that cater to their particular needs.

In this reference, we’ve included multiple VBA macro example that highlights some of the language’s most important key aspects and functionalities. Together with some tips and best practices for using VBA macros, we also addressed several frequently asked questions on the subject. Excel users can save time and effort, streamline their workflow, and get better results by adhering to these best practices and utilizing VBA macros.  If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo