Excel VBA: Renaming Active Sheet with Active Cell Value

This Excel VBA macro rename an active sheet with an active cell value.

Macro Example

Sub RenameActiveSheetWithActiveCell()

  On Error Resume Next

  ActiveSheet.Name = ActiveCell.Value

End Sub

Description

On Error Resume Next

Sub...

  On Error Resume Next

A run-time error will occur at the 5th line with some reasons.
The 3rd line aims to ignore this run-time error.

[Application.]ActiveSheet

  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-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.Name (Worksheet.Name)

  ActiveSheet.Name...

The ActiveSheet.Name (Worksheet.Name property) returns or sets a String value that represents the active sheet name.

[Application.]ActiveCell

  ActiveSheet.Name = ActiveCell...

The [Application.]ActiveCell property returns the active Range object that represents the only current active cell.

obj-Excel-Application-ActiveCell

And, the ActiveCell property is also a member of the Excel.Global class.

obj-Excel-Global-ActiveCell

So, we can omit the Application property too.

ActiveCell.Value (Range.Value)

  ActiveSheet.Name = ActiveCell.Value

The ActiveCell.Value (Range.Value property) returns or sets a Variant value that represents the value of the active cell.

obj-Excel-Range-Value

In this macro, I am setting the ActiveCell.Value to the ActiveSheet.Name. So, an active sheet will rename with the active cell value.

Sub RenameActiveSheetWithActiveCell()

  On Error Resume Next

  ActiveSheet.Name = ActiveCell.Value

End Sub

Properties

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

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007