How to select all tabs in Excel

Selecting all tabs allows you to perform operations on multiple sheets simultaneously, saving time and effort. For example, if you need to copy or move data from one sheet to another, selecting all tabs enables you to accomplish the task in a single operation rather than repeating it for each sheet individually.

Imagine you are working as a financial analyst and have access to the sales data for five different stores. As part of your analysis, you need to perform a task that requires selecting all the tabs simultaneously. In this tutorial, we will explore the steps to efficiently select all the tabs at once, allowing you to accomplish your objectives effectively.

Method 1 – By using the VBA code

Step 1 – Add a module

  • For adding a module, navigate to the Developer tab.
  • After that, click on the first option named Visual Basic.
  • You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
  • Then, click on the Insert tab in this window and click on the Module option.
  • Now, a new module would open.

Step 2 – Write the code

  • Copy and paste the following code into the module

Sub SelectAllSheets()

    Dim ws As Worksheet

    ‘ Loop through all worksheets in the workbook

    For Each ws In ThisWorkbook.Worksheets

‘Select the sheet without deselecting the previously selected sheets

ws.Select False 

    Next ws

End Sub

  • Then close the VBA Editor by using “Alt+Q”.

Step 3 – Run the macro

  • Use the “Alt+F8” key to open the “Run a macro” dialogue box.
  • Then, click on the macro named “SelectAllSheets”.
  • After doing that, click on the “Run” button and all the sheets will be selected.

Method 2 – By using the context menu

Step 1 – Open the context menu

  • Right-click on any sheet tab to open a context menu.

Step 2 – Select All Sheets

  • Now, click on the last option named “Select All Sheets” or simply press the “S” key on your keyboard.