Automatically Name New Tabs in Excel

If you work with Excel and regularly update a workbook by copying an existing sheet, you can use a simple macro to update the name of the new Tab each time. For example, creating a new Monthly Forecast or Expenses sheet. Or anytime you're creating a template for onboarding, projects, UAT scripts, data collection, surveys - anything where you might use the same sheet multiple times but with different names - this is a game changer!

Sara Burton
|
17 steps
|
4 minutes

Microsoft

Don't panic if you're not an expert in Macros - or if you're a Macro-phobe! - this guide gives you everything you need and no Macro knowledge is needed.

If you prefer to view this guide as a video, click [here](https://scribehow.com/viewer/Automatically_Name_Tabs_in_Excel__PWRDQe-ZRMaxDzBDESt1qw?scrollToActionId=20489039)

Enable Macros in the Workbook

Save the spreadsheet as a Macro enabled version Go to "File"

Click "Save a Copy"

Select "Excel Macro-Enabled Workbook (\*.xlsm)"

Click "Save"

Add the Macro

Right Click on the Tab you want to name

Select "View Code"

You'll see this window open

Copy the code below: ``` Private Sub Worksheet_Change(ByVal Target As Range) ' === EDIT THESE TWO THINGS ONLY === ' 1. Cell that controls the sheet name: Const TRIGGER_CELL As String = "B6" ' 2. Optional prefix (leave "" for no prefix): Const NAME_PREFIX As String = "" ' =================================== Dim newName As String ' Only run when the trigger cell changes If Intersect(Target, Me.Range(TRIGGER_CELL)) Is Nothing Then Exit Sub ' Build the new name newName = NAME_PREFIX & Me.Range(TRIGGER_CELL).Value ' If blank, stop here If Trim(newName) = "" Then Exit Sub ' Remove illegal characters newName = Replace(newName, "/", "-") newName = Replace(newName, "\", "-") newName = Replace(newName, ":", "-") newName = Replace(newName, "*", "") newName = Replace(newName, "?", "") newName = Replace(newName, "[", "(") newName = Replace(newName, "]", ")") newName = Replace(newName, """", "") ' Trim to Excel’s max sheet name length If Len(newName) > 31 Then newName = Left(newName, 31) ' Rename the sheet On Error Resume Next Me.Name = newName On Error GoTo 0 End Sub ```

Paste into the right hand window pane.

Edit the Macro

Edit this line to contain the cell you want to reference in the Excel Tab - currently its referencing B6. But update to whichever cell you want, ensuring its within the " "

If you want a prefix to your tab name, add it here. Eg "Risk Assessment..." if not just leave as Const NAME_PREFIX As String = ""

Save by clicking [[Ctrl]] + [[S]]

Minimize the code Window

Test!

Test! Enter data into the cell you've identified to name the tab In this case, B6

And Bingo! The tab is now named as expected.

If you need to change, just right click on the tab and select "View Code" again

Now, whenever you copy that sheet, it will be named according to this convention.

Making people and processes more efficient is what I do! To see other ways I help support businesses improve their operations, connect with me on LinkedIn [here](https://www.linkedin.com/in/sara-burton-coo/). If you've enjoyed this Scribe, why not try it for yourself? Check it out [here](http://www.scribehow.com/lp/home?via=sara) And to see more of my FREE Scribes check out my community [here](https://scribehow.com/by/Sara_Burton__zA7LpIwHQZqpiJyCDq2veA?via=sara).