Excel Macro: Selecting multiple sheets from Active Sheet to Last

This Excel VBA macro selects multiple sheets from an active sheet to the last sheet.

Macro Example

Sub SelectsMultipleSheets()

  Dim i As Long

  For i = ActiveSheet.Index To Sheets.Count
    Sheets(i).Select Replace:=False
  Next i

End Sub

Description

Variable Declaration

Sub...

  Dim i As Long

The 3rd line declares a long integer type variable named i.

Starts Looping

Sub...

  Dim i As Long

  For i = ActiveSheet.Index To Sheets.Count

The 5th line starts to loop through the sheets

The counter variable i starts at the index number of the active sheet, and is reduced by 1 on each iteration of the loop, ending after the value of variable reaches the Sheets.Count.

[Application.]ActiveSheet

  For i = ActiveSheet...

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

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

obj-Excel-Global-ActiveSheet

ActiveSheet.Index (Worksheet.Index or Chart.Index)

  For i = ActiveSheet.Index To Sheets.Count

The ActiveSheet.Index (Worksheet.Index property or Chart.Index property) returns a Long value that represents the index number of the Worksheet object or Chart object.

obj-Excel-Worksheet-Index

obj-Excel-Chart-Index

Sheets.Count

  For i = ActiveSheet.Index To Sheets.Count

The Sheets.Count property returns a Long value that represents a number of the sheets.

Selecting Sheets

Sub...

  Dim i As Long

  For i = ActiveSheet.Index To Sheets.Count
    Sheets(i).Select Replace:=False

The 6th line select a sheet.

Sheets(i) (Sheets._Default(i))

    Sheets(i)...

The Sheets._Default property returns a single Worksheet or Chart object from the Sheets collection object.

The _Default property is a default member of the Sheets collection object. We can see a little blue marble beside its icon in the Object Browser.

So we can omit the ._Default .

Sheets(i).Select (Worksheet.Select or Chart.Select)

    Sheets(i).Select Replace:=False

The Sheets(i).Select (Worksheet.Select or Chart.Select method) selects the worksheet or chart sheet.

This method extends the current selection to include any previously selected sheets by setting the parameter:Replace to False.

Loops Back and End the With Structure

Sub...

  Dim i As Long

  For i = ActiveSheet.Index To Sheets.Count
    Sheets(i).Select Replace:=False
  Next i

The 7th line loops back to select the next Sheet.
After the last sheet is selected, this macro ends.

Sub SelectsMultipleSheets()

  Dim i As Long

  For i = ActiveSheet.Index To Sheets.Count
    Sheets(i).Select Replace:=False
  Next i

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007