Excel VBA Group Worksheets: How to Group & Ungroup

VBA & macros · Updated November 22, 2026

To group worksheets in Excel VBA, use the Sheets collection and set the Select property on multiple sheets, or set the Worksheets collection’s Group method (though the latter is not a native VBA method—grouping is done via Select with multiple sheets). The simplest code is: Sheets(Array("Sheet1", "Sheet2")).Select. This selects both sheets, which automatically groups them in the Excel interface. To ungroup, select a single sheet: Sheets("Sheet1").Select.

Grouping worksheets in Excel lets you apply changes (like formatting, data entry, or printing) to multiple sheets at once. In VBA, you can automate this grouping to save time and reduce errors, especially when working with monthly reports or multi-sheet templates. This article explains how to group, ungroup, and work with grouped sheets in VBA, including common pitfalls and practical examples.

What Does Grouping Worksheets Mean in Excel?

When you group worksheets, Excel treats the selected sheets as a single unit. Any action you perform on one sheet—such as typing in a cell, formatting, or inserting a row—is applied to all grouped sheets simultaneously. This is useful for creating consistent layouts across multiple sheets, like setting up a monthly budget with identical headers and formulas.

In the Excel interface, you can group sheets by holding Ctrl and clicking sheet tabs, or by right-clicking a tab and choosing “Select All Sheets.” The title bar shows “[Group]” to indicate grouping. In VBA, grouping is achieved by selecting multiple sheets with the Select method. There is no explicit Group method in the VBA object model; grouping is a side effect of multi-sheet selection.

How to Group Worksheets with VBA: Basic Syntax

To group specific sheets, use the Sheets or Worksheets collection with an array of sheet names or index numbers. The following code groups the first three sheets:

Sub GroupSheets()
    ' Select multiple sheets to group them
    Sheets(Array(1, 2, 3)).Select
End Sub

If you prefer using sheet names (common when sheets are renamed), use:

Sub GroupNamedSheets()
    ' Group sheets by name
    Sheets(Array("January", "February", "March")).Select
End Sub

Note: The Sheets collection includes both worksheets and chart sheets. If you want to ensure only worksheets, use Worksheets instead. Both work for grouping, but Worksheets is safer if you have chart sheets.

How to Ungroup Worksheets with VBA

Ungrouping is simply selecting a single sheet. The following code ungroups any grouped sheets by selecting only “Sheet1”:

Sub UngroupSheets()
    ' Select a single sheet to ungroup
    Sheets("Sheet1").Select
End Sub

If you don’t know which sheet is active, you can ungroup by selecting the active sheet (which is always one of the grouped sheets):

Sub UngroupActiveSheet()
    ' Ungroup by selecting the active sheet alone
    ActiveSheet.Select
End Sub

This works because selecting a single sheet removes the grouping.

How to Group All Worksheets in a Workbook

To group every worksheet in the workbook, you can loop through all sheets and build an array, or use a simpler approach with the Select method on all sheets. However, there’s no direct “Select All” method, so you need to construct an array of all sheet names:

Sub GroupAllSheets()
    Dim ws As Worksheet
    Dim sheetNames() As String
    Dim i As Long
    
    ' Count worksheets
    i = 0
    For Each ws In ThisWorkbook.Worksheets
        i = i + 1
        ReDim Preserve sheetNames(1 To i)
        sheetNames(i) = ws.Name
    Next ws
    
    ' Select all worksheets to group them
    ThisWorkbook.Worksheets(sheetNames).Select
End Sub

This code collects all worksheet names into an array and then selects them, grouping all sheets. Note that this will fail if the workbook has no worksheets (unlikely).

Practical Example: Formatting Multiple Sheets at Once

Grouping is often used to apply the same formatting to several sheets. Here’s a complete macro that groups the first three sheets, sets the font size and column width, then ungroups:

Sub FormatGroupedSheets()
    ' Save a copy of the workbook before running macros that modify data
    ' (macros cannot be undone with Ctrl+Z)
    
    ' Group first three sheets
    Sheets(Array(1, 2, 3)).Select
    
    ' Apply formatting to all grouped sheets
    With Selection
        .Font.Size = 11
        .Columns("A:A").ColumnWidth = 20
    End With
    
    ' Ungroup
    Sheets(1).Select
End Sub

In this example, Selection refers to the grouped sheets (since they are selected). Any formatting applied to Selection affects all selected sheets. After ungrouping, the changes remain on each sheet.

Common Problems and How to Avoid Them

Grouping in VBA can cause unexpected issues if not handled carefully. Here are common problems and solutions:

  • Problem: “Select method of Worksheet class failed” error. This happens if you try to select a sheet that is not visible or if the workbook is protected. Solution: Ensure the sheet is visible (ws.Visible = xlSheetVisible) and unprotect the workbook if needed.
  • Problem: Grouping persists after macro ends. If you forget to ungroup, the sheets remain grouped in the UI. Solution: Always ungroup at the end of the macro, or use Application.ScreenUpdating to hide the grouping effect.
  • Problem: Selecting a sheet that is not in the array. If you reference a sheet name that doesn’t exist, VBA will error. Solution: Use error handling (e.g., On Error Resume Next) or check if the sheet exists before grouping.
  • Problem: Grouping all sheets when some are hidden. Hidden sheets cannot be selected directly. Solution: Unhide them first or use a loop to set Visible property.

Alternatives to Grouping in VBA

If you need to apply changes to multiple sheets without visually grouping them, consider these alternatives:

  • Loop through sheets: Use a For Each loop to apply changes to each sheet individually. This gives more control and avoids the grouping side effects.
  • Use the FillAcrossSheets method: This is a built-in Excel feature that copies content from one sheet to others. In VBA, you can call ActiveSheet.FillAcrossSheets Worksheets(Array("Sheet2", "Sheet3")) to fill data from the active sheet to others without grouping.
  • Use 3D references in formulas: For calculations across sheets, you can use formulas like =SUM(Sheet1:Sheet3!A1) without grouping.

Each method has its use case. Grouping is fastest for manual, one-off edits; looping is more flexible for complex logic; and FillAcrossSheets is ideal for copying specific ranges.

Frequently Asked Questions

Can I group worksheets in VBA without selecting them?

No, grouping in Excel VBA is inherently tied to selection. There is no separate “Group” method; selecting multiple sheets creates the group. If you want to apply changes without visual grouping, loop through sheets or use FillAcrossSheets.

How do I check if sheets are currently grouped in VBA?

You can check the ActiveWindow.SelectedSheets property. If it returns more than one sheet, they are grouped. For example: If ActiveWindow.SelectedSheets.Count > 1 Then MsgBox "Sheets are grouped".

Does grouping worksheets affect printing in VBA?

Yes, when sheets are grouped, print settings (like page orientation and margins) apply to all grouped sheets. In VBA, you can set ActiveSheet.PageSetup properties, which will affect all selected sheets. Remember to ungroup before printing only one sheet.

Can I group non-adjacent sheets in VBA?

Yes, you can group any combination of sheets by listing them in the array. For example: Sheets(Array("Sheet1", "Sheet3", "Sheet5")).Select groups non-adjacent sheets.

Why does my macro stop when I try to select a sheet that is hidden?

Hidden sheets cannot be selected. To group hidden sheets, you must first unhide them using ws.Visible = xlSheetVisible in your code, then select them. Alternatively, you can set the Visible property after grouping, but selection will fail if the sheet is hidden.

Is there a way to group sheets by index number instead of name?

Yes, you can use index numbers in the array, as shown in the first example: Sheets(Array(1, 2, 3)).Select. The index corresponds to the sheet’s position in the tab bar, starting from 1 for the leftmost sheet.

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.