Rename Each Worksheet Based On Its A1 Value

May 31, 2021 - by Bill Jelen

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:

   

   ---

   ---

   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:

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

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.