Excel

Select Multiple Sheets

Ease of Use

Easy

Version tested with

2000 

Submitted by:

mvidas

Description:

This code will select all sheets without putting the sheet names into an array. 

Discussion:

This code will help you select multiple sheets in a workbook, without needing to know the names of them. This is useful if you need to print multiple pages in a macro, but have a variable number/names of worksheets. It is also good if you have a workbook with a lot of worksheets that need to be printed at once (for a total page count), but want to avoid statements like Sheets(Array("Sheet1", "Sheet2", "Sheet3... This can also be easily customized to not include specific sheets in the selection (see example). This is meant more to be part of a bigger macro than a standalone macro. It's only real standalone function would be to assign it to a button or keyboard-shortcut to select all sheets easier. 

Code:

instructions for use

			

Sub SelectAllSheets() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Sheets 'False to extend the current selection to include ' any previously selected objects and the specified object ws.Select False Next ws Application.ScreenUpdating = True End Sub

How to use:

  1. Copy code.
  2. Open workbook to copy code to.
  3. In Excel, press Alt + F11 to open the Visual Basic for Applications Editor (VBE).
  4. Click the Insert menu option.
  5. Click Module.
  6. Paste code in blank code pane on right side of VBE window.
  7. Press Alt + Q to close the VBE.
 

Test the code:

  1. Open the sample file "Example-Select All Sheets.xls"
  2. In the sample file, it starts on the 3rd sheet (of 26 total sheets)
  3. On that sheet are 6 buttons: 3 buttons to run macros, and 3 smaller buttons to show you the code for the macros.
  4. The first button renames every sheet to random names, but small enough so that you can still see all of them at the bottom. This was added to show that the sheets can be selected programmatically without knowing all of their names.
  5. The second button is the "Select all sheets" button. Note that this does the same as right clicking on a sheet tab and selecting Select All Sheets, but there is no direct code in vba to do the same. The file just shows how it can work when called-from/added-to existing code.
  6. The third button is "Select all sheets but the first and second". This does the same as the second button, but does not include the first two sheets.
  7. Also included are three "Show Code" buttons to show you how the macros are written.
 

Sample File:

Example-Select All Sheets.zip 21.4KB 

Approved by mdmackillop


This entry has been viewed 286 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express