Rename Each Worksheet Based On Its A1 Value


May 31, 2021 - by Bill Jelen

Rename Each Worksheet Based On Its A1 Value

Challenge: You have a workbook that has numerous worksheets. The title of each worksheet is in cell A1. You want to name each worksheet based on its cell A1 value.

Solution: You can quickly and automatically solve this problem by using a tiny bit of VBA code. Here’s how:

  1. Press Alt+F11.
  2. Press Ctrl+G to open the immediate pane.
  3. Type the following code and then press Enter:

    e9781615474011_i0253.jpg

    This is actually a three-line macro, with the lines separated with colons.


Additional Details: If any value in cell A1 contains more than 31 characters, the name is shortened to 31 characters.

If any worksheet has an illegal character in cell A1, the macro stops with an error. For worksheet names, the illegal characters are ’, *, /, :, ?, [, \, and ]. To simply skip the worksheets that contain illegal characters, you can use this macro:

e9781615474011_i0254.jpg

To use alternate characters instead of the illegal characters, use this macro:

e9781615474011_i0255.jpg
e9781615474011_i0256.jpg

Summary: You can use a short macro to rename worksheets.

Title Photo: Jon Tyson on Unsplash


This article is an excerpt from Excel Gurus Gone Wild.



Bill Jelen is the author / co-author of:
MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.