Excel VBA: Getting the Next Sheet Name

Sometimes I need to get the name of a next sheet.

Macro Example

The following Excel VBA macro displays it in a message box.

Sub GetNextSheetName()

  With ActiveSheet
    If .Index < Sheets.Count Then
      MsgBox .Next.Name
    End If
  End With
 
End Sub

Description

Starts the With Structure

Sub...

  With ActiveSheet

The 3rd line starts the With…End With structure.

This statement tells Excel that any action applies to an active sheet (Worksheet object or Chart object).

ActiveSheet property

obj-Excel-Application-ActiveSheet

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

obj-Excel-Global-ActiveSheet

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

Checks If the Index is Smaller than Sheets.Count

Sub...

  With ActiveSheet
    If .Index < Sheets.Count Then

A run-time error will occur at the 5th line (.Next.Name), if the active sheet is the last (there is no next sheet). The 4th line aims to ignore this run-time error.

Worksheet.Index or Chart.Index

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

obj-Excel-Worksheet-Index

obj-Excel-Chart-Index

Getting the Name of a Next Sheet

Sub...

  With ActiveSheet
    If .Index < Sheets.Count Then
      MsgBox .Next.Name

Worksheet.Previous or Chart.Previous

The Worksheet.Next property or Chart.Next property (ActiveSheet.Next) returns the Worksheet or Chart object that represents the next sheet.

Worksheet.Name or Chart.Name

The Worksheet.Name property or Chart.Name property (Previous.Name) returns or sets a String value that represents the Worksheet name or Chart name.

The code: ActiveSheet.Next.Name is set to the parameter of a MsgBox function.

So, a message box shows the name of a next sheet.

Ends the With

Sub...

  With ActiveSheet
    If .Index < Sheets.Count Then
      MsgBox .Next.Name
    End If
 End With

The 7th line ends the With…End With structure.

After the 7th line is evaluated, this macro ends.

Sub GetNextSheetName()

  With ActiveSheet
    If .Index < Sheets.Count Then
      MsgBox .Next.Name
    End If
  End With
 
End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007