Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Selecting All Visible Worksheets in a Macro.

Selecting All Visible Worksheets in a Macro

by Allen Wyatt
(last updated January 7, 2017)

In Excel, selecting all the visible worksheets is as easy as right-clicking on any sheet tab and choosing Select All Sheets. However, accomplishing the same task with VBA code is more difficult.

Excel's online help suggests using the Array function with the Sheets collection to select sheets by name. This works great when you know the names of each sheet in the workbook. This poses a problem when you want to create generic code to select all sheets for any workbook. The good news is that you can use a variant of Microsoft's technique to reference sheets by index number. Below is the code:

Sub SelectSheets()
    Dim myArray() As Variant
    Dim i As Integer
    For i = 1 To Sheets.Count
        ReDim Preserve myArray(i - 1)
        myArray(i - 1) = i
    Next i
    Sheets(myArray).Select
End Sub

This works great, unless the workbook contains hidden sheets, where Sheets(i).Visible = False. Of course, the above code can be adapted to ignore hidden worksheets:

Sub SelectSheets()
    Dim myArray() As Variant
    Dim i As Integer
    Dim j As Integer
    j = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Visible = True Then
            ReDim Preserve myArray(j)
            myArray(j) = i
            j = j + 1
        End If
    Next i
    Sheets(myArray).Select
End Sub

However, there is a little known parameter of the Select method: the Replace parameter. By using the Replace parameter, selecting all visible sheets becomes much easier:

Sub SelectSheets1()
    Dim mySheet As Object
    For Each mySheet In Sheets
        With mySheet
            If .Visible = True Then .Select Replace:=False
        End With
    Next mySheet
End Sub

Note that mySheet is defined as an Object data type, instead of a Worksheet data type. This is done because in testing I encountered a problem with Chart sheets—they wouldn't be selected because they weren't of a Worksheet type.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3058) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Selecting All Visible Worksheets in a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Automatic Numbers with Leading Zeroes

Word's automatic numbering formats allow you to easily create lists that have one leading zero. If you want more than one ...

Discover More

A Shortcut for Switching Focus

While not technically an Excel-only tip, the shortcuts described in this tip will help you switch focus from your workbook to ...

Discover More

Drop Shadows for Tables

When adding borders and shading to a document's elements, Word allows you to quickly add drop shadows to paragraphs, text ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Forcing Manual Calculation For a Workbook

If you have a large, complex workbook, you may want to make sure that it is always calculated manually instead of ...

Discover More

Running a Procedure when a Workbook is Opened

Ever want to have Excel run a procedure whenever you open a workbook? It's not as difficult as you might think. Here's how.

Discover More

Sheets for Months

One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you need ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 8?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share