Excel VBA: Looping Through the Selected Sheets

Sometimes I want to loop through only the selected sheets when I make a Excel macro.

Macro Examples

The following Excel VBA macro displays each sheet name in a message box.

Sub GetSelectedSheetsName()

  Dim sh As Object

  For Each sh In ActiveWindow.SelectedSheets
    MsgBox sh.Name
  Next sh

End Sub

Description

Variable Declaration

Sub...

  Dim sh As Object

The 3rd line declares an object variable named sh represents a Worksheet or a Chart object.

Starts Looping

The 5th line starts to loop through each elements (=Worksheet or Chart) of a collection (=Sheets).

Sub...

  Dim sh As Object

  For Each sh In ActiveWindow.SelectedSheets

ActiveWindow.SelectedSheets, this is the code to get only the selected sheets.

(Application.)ActiveWindow

The Application.ActiveWindow property returns the Window object that represents an active window.

obj-Excel-Application-ActiveWindow

The ActiveWindow property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-ActiveWindow

ActiveWindow.SelectedSheets (Window.SelectedSheets)

The ActiveWindow.SelectedSheets (Window.SelectedSheets property) returns the Sheets collection object that represents all the selected sheets in a window.

obj-Excel-Window-SelectedSheets

Show Message Box

The 6th line displays the sheet name in a message box.

Sub...

  Dim sh As Object

  For Each sh In ActiveWindow.SelectedSheets
    MsgBox sh.Name

The Worksheet.Name property or…

obj-Excel-Worksheet-Name

the Chart.Name property…

obj-Excel-Char-Name

returns a string value that represents the worksheet name or the chart name.

Looping Back

The 7th line loops back to get the next sheet.

Sub...

  Dim sh As Object

  For Each sh In ActiveWindow.SelectedSheets
    MsgBox sh.Name
  Next sh

After every sheet is evaluated, this macro ends.

Sub GetSelectedSheetsName()

  Dim sh As Object

  For Each sh In ActiveWindow.SelectedSheets
    MsgBox sh.Name
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/018455.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007