Retrieve Worksheet

One of the most common situations where VBA is utilized is in the manipulation of Excel worksheets.

To manipulate a worksheet, you first need to retrieve it.

VBA provides multiple methods to retrieve a worksheet, including specifying by name, by index, or getting the currently active worksheet.

This page explains how to retrieve a worksheet using VBA with specific examples.

Methods to Retrieve a Worksheet

Retrieve by Specifying the Name

First, let’s explain how to retrieve a worksheet using its sheet name.

This method is the most intuitive and straightforward as it allows retrieval based on the name.

Use the Sheets property of the Workbook class to retrieve the worksheet.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

In the example above, ThisWorkbook refers to the current workbook, and the Sheets property is used to retrieve the worksheet.

By specifying the sheet name as an argument, you can retrieve the worksheet with that name.

If the specified sheet does not exist, an error will be output.

Retrieve by Specifying the Index

Next, let’s explain how to retrieve a worksheet using its index.

Similar to the method of retrieving by name, use the Sheets property of the Workbook class to retrieve the worksheet by index.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)

By specifying the index instead of the sheet name to the Sheets property, you can retrieve the worksheet at that index.

The index starts at 1, so in the example above, the first worksheet in the workbook is retrieved.

Retrieve the Currently Active Worksheet

There is also a method to retrieve the worksheet that is currently active (displayed on the screen) when VBA is executed.

Use the ActiveSheet property to retrieve the currently active worksheet.

Dim ws As Worksheet
Set ws = ActiveSheet

Unlike the previous two methods where the Sheets property of the Workbook class was used to retrieve the worksheet, the ActiveSheet property can be accessed directly from the Application class.

Therefore, simply typing ActiveSheet allows you to retrieve the currently active worksheet.

However, since the target worksheet may change depending on the environment in which it is executed, it is advisable to avoid using this method carelessly. It is recommended to use methods that retrieve by name or index whenever possible.

For detailed usage of the ActiveSheet property, refer to the page below.

Retrieve All Worksheets in an Excel File Using an Iterator

It is also possible to retrieve all worksheets in an Excel file using the For Each statement.

Dim sheet As Worksheet

For Each sheet In ThisWorkbook.Worksheets
  Debug.Print sheet.Name
Next

ThisWorkbook.Worksheets is an iterable object, and you can use the For Each statement to retrieve all worksheets.

In the example above, sheet is sequentially assigned all worksheets in the Excel file, and Debug.Print sheet.Name outputs the name of each worksheet.

Examples

Let’s look at specific examples using the methods mentioned above.

Retrieve by Specifying the Name

First, let’s look at the simplest example of retrieving a worksheet by name.

Dim ws As Worksheet, cellValue As String
Set ws = ThisWorkbook.Sheets("Sheet1")
cellValue = ws.Range("A1").Value

Debug.Print cellValue

In the example above, ThisWorkbook.Sheets("Sheet1") retrieves the worksheet named Sheet1, and the Range property is used to get the value of cell A1.

Perform Operations on All Sheets

Next, let’s look at an example of performing operations on all worksheets.

Dim sheet As Worksheet, concatenated As String

For Each sheet In ThisWorkbook.Worksheets
  concatenated = concatenated & sheet.Name & ","
Next

In the example above, the For Each statement is used to perform operations on all worksheets.

concatenated sequentially concatenates the names of all worksheets, separated by commas.

Summary

We have explained how to retrieve worksheets using VBA.

There are multiple methods to retrieve worksheets, and the maintainability and readability vary depending on the method, so it is important to use them appropriately.

In particular, the method of retrieving the currently active worksheet should be avoided as it may change the target worksheet depending on the environment in which it is executed.

For details on defining constants and maintainability, refer to the page below.

Refer to the code introduced here and use the appropriate method to retrieve worksheets according to your tasks.