Excel VBA Array of Worksheets: Create and Use One

VBA & macros · Updated November 21, 2026

In Excel VBA, you can work with several worksheets as a group by passing an array of sheet names to Worksheets, or store worksheet objects in an array when you need to process them one at a time. Use the first approach for a quick grouped selection; use an object array or collection when your code needs to loop through specific sheets.

What an array of worksheets means

VBA does not require a special “worksheet array” type for every task. The phrase usually refers to one of two things: an array of worksheet names, or an array whose elements are references to Worksheet objects. The distinction matters because the two approaches support different operations.

  • An array of names is useful for selecting a group of worksheets together. The values are strings such as "Summary" and "January".
  • An array of Worksheet objects is useful for keeping references to particular sheets and performing an operation on each one.

Both approaches can work with worksheets in the current workbook. Qualifying the workbook explicitly, as in ThisWorkbook.Worksheets, helps ensure that VBA uses the workbook containing the macro rather than whichever workbook happens to be active. A worksheet is a tabular sheet; a chart sheet is not a worksheet, so use the Worksheets collection when the code is intended to target worksheets only.

Select several worksheets by name

For a straightforward group selection, pass an array of sheet names to the Worksheets collection and call Select. In this example, the names must match worksheet tab names in the workbook that contains the macro:

Sub SelectMonthlySheets()
    Dim sheetNames As Variant

    ' Save a copy of the workbook before running a macro.
    sheetNames = Array("Summary", "January", "February")
    ThisWorkbook.Activate
    ThisWorkbook.Worksheets(sheetNames).Select
End Sub

The built-in Array function creates a Variant array. The sheet names can be listed directly inside it, or assigned to a Variant variable as above. Using Worksheets(sheetNames).Select selects those tabs together; it does not create a permanent array of worksheet objects. In Excel, the selected tabs form a group, and many edits made to the active sheet are also applied to the other selected sheets.

That grouping behavior is helpful for applying the same formatting or entering the same value in the same cell across multiple sheets, but it can also cause accidental changes. After the grouped operation, select one sheet on its own before continuing. Do not use this method just to loop through sheets: grouping is a user-interface action and can have side effects.

Store worksheet objects in a VBA array

If you want to refer to worksheets individually, declare an array with the Worksheet type and assign each element with Set. This example prepares three specific sheets and writes a status value to cell A1 on each one:

Sub MarkSelectedSheets()
    Dim targets(0 To 2) As Worksheet
    Dim ws As Worksheet

    ' Save a copy of the workbook before running a macro.
    Set targets(0) = ThisWorkbook.Worksheets("Summary")
    Set targets(1) = ThisWorkbook.Worksheets("January")
    Set targets(2) = ThisWorkbook.Worksheets("February")

    For Each ws In targets
        ws.Range("A1").Value = "Reviewed"
    Next ws
End Sub

The array bounds here are explicit: 0 To 2 gives three slots. Worksheet variables are object references, which is why each assignment uses Set. The loop acts on each referenced sheet without selecting or activating it. That is generally a better pattern for automation because it makes the target explicit and does not depend on the active tab.

The sample writes to cells, so it changes workbook content and can overwrite existing values in A1. Check the target cells and save a separate copy before running it. Macros cannot be undone with Ctrl+Z, so closing without saving may be the only way to discard unintended changes.

Loop through worksheets without building an array

If the task applies to every worksheet in a workbook, an array is usually unnecessary. The Worksheets collection can be traversed directly:

Sub ProcessEveryWorksheet()
    Dim ws As Worksheet

    ' Save a copy of the workbook before running a macro.
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

This example prints each worksheet name in the Immediate window in the VBA editor. Replace Debug.Print ws.Name with the operation you need. The loop includes hidden worksheets as well as visible ones, because both are members of the workbook’s Worksheets collection. If only certain sheets should be changed, add a condition rather than assuming that every tab is suitable.

A direct loop is simpler and less error-prone than maintaining a separate list when the requirement truly is “all worksheets.” An explicit array is more appropriate when the code should touch only a chosen subset, or when the order and membership of the targets are part of the task.

Use a collection for a changing set of sheets

A fixed-size object array works well when the number of target sheets is known. If code needs to build a set of worksheet references as it runs, a VBA Collection can be more convenient. A collection can hold object references and grow as items are added:

Sub ProcessChosenSheets()
    Dim targets As Collection
    Dim ws As Worksheet

    ' Save a copy of the workbook before running a macro.
    Set targets = New Collection
    targets.Add ThisWorkbook.Worksheets("Summary")
    targets.Add ThisWorkbook.Worksheets("February")

    For Each ws In targets
        Debug.Print ws.Name
    Next ws
End Sub

This pattern is useful when conditions determine which sheets qualify. For example, code could inspect each worksheet and add only sheets whose names follow a naming rule, then process the resulting collection. A collection is not the same as a native VBA array: it has its own methods and does not use numeric array bounds in the same way. For a short, fixed list, the array is often easier to understand.

Choose the right approach for the task

Decide whether you need a grouped selection, a list of object references, or simply a loop over every sheet. The goal should determine the data structure, rather than using an array just because several worksheets are involved.

  • Use Worksheets(Array("A", "B")).Select when a person needs multiple tabs selected together. Be aware that edits can affect the whole group.
  • Use a typed Worksheet array when a short, fixed set of sheets must be handled individually and in a known order.
  • Use a Collection when the set of sheets is assembled dynamically or may vary in size.
  • Use For Each ws In ThisWorkbook.Worksheets when every worksheet is in scope.

For most unattended macros, looping over object references is safer than selecting sheets. Selection is sometimes necessary for a user-facing workflow, but many worksheet operations—such as reading a range, changing a value, or applying formatting—can be performed by qualifying the worksheet directly.

Names, workbook references, and common errors

A frequent error is using a name that does not exactly match a worksheet tab. A renamed, misspelled, or deleted sheet can cause a “Subscript out of range” error when VBA tries to retrieve it. Confirm the tab names, and make sure the referenced workbook is the intended one. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel, which may be different.

Another common source of trouble is assuming that selecting sheets is required before changing their contents. It is not. For example, ws.Range("B2").Value = 10 operates on the worksheet referenced by ws, even when that sheet is not active. Direct references reduce dependence on Excel’s current selection.

Some other conditions can prevent a selection or change:

  • A worksheet that is hidden cannot be selected until it is made visible. Hidden sheets can still be addressed and processed directly by VBA in many operations.
  • Workbook structure protection can prevent adding, deleting, moving, or selecting sheets in certain ways.
  • A misspelled name or a reference to a sheet in the wrong workbook can fail even when a sheet with a similar name is visible elsewhere.
  • Grouping worksheets changes Excel’s selection state. Before later manual edits, check that only the intended tab is selected.

When code is intended to work with a workbook supplied by someone else, consider validating that each required sheet exists before processing it. This makes failures easier to explain than allowing an unexplained runtime error. For a macro that changes data, test on a copy first; VBA actions generally are not recorded in Excel’s normal Undo history.

Excel version and platform considerations

The worksheet collections, VBA arrays, and examples shown here apply to desktop Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web, so a workbook opened there cannot execute this code. Desktop Excel for Mac also supports VBA, although some features and platform-specific automation differ from Windows; keyboard shortcuts for opening the VBA editor can differ as well.

To retain VBA code in a workbook, save it in a macro-enabled format such as .xlsm. Excel may display a security warning when opening a macro-enabled file. Enable macros only when the file and its source are trusted; a macro can change workbook data and may perform other actions available to VBA.

Frequently Asked Questions

How do I create an array of worksheet names in VBA?
Declare a Variant and assign it with Array("Sheet1", "Sheet2"). You can pass that variable to Worksheets to select the named sheets as a group. The array contains names, not Worksheet objects.

How do I loop through an array of worksheets?
Declare a Worksheet array, assign each element with Set, and use For Each ws In targets. If you mean every worksheet in the workbook, loop directly over ThisWorkbook.Worksheets instead.

Can I select multiple worksheets with VBA?
Yes. Use Worksheets(Array("Sheet1", "Sheet2")).Select, with names that exist in the target workbook. The sheets become grouped, so edits to the active sheet may also affect the other selected sheets.

What is the difference between Sheets and Worksheets?
Worksheets refers to worksheet tabs. Sheets can include both worksheets and chart sheets. Use Worksheets when your code expects ordinary grid-based sheets.

Why does VBA report “Subscript out of range” for a worksheet?
Usually, the requested name or index is not present in the collection being referenced. Check the spelling and workbook qualification, and verify that the sheet has not been renamed or deleted.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.