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")).Selectwhen 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.Worksheetswhen 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
- Excel Split Cell Into Multiple Cells: Methods That Work
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Best Macro Recorder for Gaming: Tools for Every SetupFor most players who already use compatible Logitech gear, Logitech G HUB is the best starting pick: it…
- Excel VBA: How to Enable AutoFilterTo enable AutoFilter with VBA, select the worksheet range that includes the column headers and run its AutoFilter…
- Set Workbook and Worksheet in VBA: How It WorksIn Excel VBA, use Set to assign a workbook or worksheet object to a variable, then refer to…
- Excel VBA Range of Object Failed? Common Causes and FixesThe Excel VBA error “Method ‘Range’ of object ‘_Worksheet’ failed” usually means VBA could not resolve the address…
- VBA InputBox vs MsgBox: OK or Cancel ExplainedYes: VBA’s standard InputBox already displays OK and Cancel buttons, while MsgBox can display them when you specify…
- Excel UserForm ComboBox: Load a List with VBATo load an Excel UserForm ComboBox from worksheet values, add a ComboBox to the form, then fill it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.