Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Selecting All Visible Worksheets in a Macro

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.

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.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Wayne Alison    14 Jun 2016, 11:08
Question: How can I make this work on several but not all the sheets in my workbook? I am doing an array formula to restore formulas on several sheets at once using "Sheets(Array(Sheet1.Name, Sheet2.Name))Select" but when sheets are hidden I get 'Select method of sheets class failed". So I need your method to work on just the sheets selected in the array formula, but not all the sheets. Then I need to re-hide just the sheets that were originally hidden. Can you help?
Andy    01 Apr 2015, 04:58
Hi Allen,

Great tips, thanks. I've landed on this page a few times over the years so thought I'd leave a comment :-)

Interesting to see the 'Replace' parameter, I hadn't come across that before.

Although easier code, it wasn't nearly as quick as your array method though!

Also, I thought that the 'Worksheet' object type encompassed the 'Sheet' and 'Chart' object types, so I'm surprised it didn't work.

Cheers,

Andy
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.