Write a Macro to Update an Excel Worksheet Tab Name

This is the 2nd part of my two-part series where I demonstrate how to create links between an Excel Worksheet Tab Name and a cell on that worksheet.

In this video tutorial, I show you how to write a Macro in the Visual Basic Editor pane to update the worksheet tab name based on a cell in that worksheet.

When writing this Macro, you need to make sure that no "diallowed" characters (e.g. ? / \ [ ], etc.) will crash the Macro.

The coding is simple and I also show you how to create a Command Button to run the Macro.


Danny Rocks
The Company Rocks

[evfred]

thank you very much for showing and explaining how to do this. I have been searching for a week to try to figger out how to do this.

[mozmal]

Thanks so much for this Danny. Been looking for a way to do this, but don't know VBA at all. You really do rock!

[DannyRocksExcels]

Thanks Ryan!

I am pleased that you enjoyed my Excel Tutorial.

Danny Rocks
The Company Rocks

[Enni-]

Danny, you're a star! This helped me soooo much! Thank you!!!

[DannyRocksExcels]

My pleasure.

I apologize for the delay in responding.

It takes some practice to understand VBA Coding. I am happy that my Excel Tutorial helped you.

Danny Rocks
The Company Rocks

[Osteele]

Hello and thank you for sharing Danny. I realize that I am a little late to the party so-to-speak, but genuinely enjoyed your video tutorial. I would like to dynamically rename two tabs based on values entered into two separate cells in another worksheet.

[PeteKreitchet]

Danny, is there any way to use this macro so that if I link cell A1 to another worksheet ("Worksheet Names", cell A1), and then change the data on this other sheet, the tab name will still automatically change on the initial worksheet? Right now, it will only change if I'm in the worksheet and change cell A1. But the idea I'm trying to solve for is that if I have 30 worksheets, I could quickly and easily change all of them from a single list of names on another worksheet. Thanks in advance!

[DannyRocksExcels]

@kfong0128
Thank you! I appreciate your feedback.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

@EssKay30
Thank you for your kind words. I am pleased that you liked this tutorial.

Danny Rocks
The Company Rocks

[bch]

Great video, thorough and easy to follow. Is there a way for us to have the worksheet name update without even having to click a button? In other words, can we make it populate after a value is entered into the cell?

[DannyRocksExcels]

@ToniZ2010
Thank you.

Danny Rocks
The Company Rocks

[johnwatkins]

Danny, love this tutorial. But after you copy and paste from one sheet into another sheet, is there a way to go back to the original sheet that your button will be on? instead on staying on the sheet that was copied.

[kimberlyeccles]

Danny this was a great, very informative video. I am using Excel 2017 and I entered the code into an existing macro and the sheet name does not change to the cell contents. Also, would this work if I have a formula in the cell I reference in the macro? I thought we were justing using the value based on the code in your macro.

[rbmwordlists]

I would also like this. Changing cell names in a contents page and have the corresponding sheet names change.

I also hyperlink from the cell to the sheet, but the hyperlink points to the defined name of the sheet, not the name of the sheet, if this is of any consideration!

[PeteKreitchet]

I should also mention that the list of names would be copy/pasted into the "Worksheet Names" sheet. So the issue seems to be activating or refreshing from within the macro without having to key in anything. Thanks again.

[animalslife]

What about changing a word on a code based on the value of a sheet's name? So the lines of codes can be automatically updated whenever I copy a new sheet and name it anew.

[DannyRocksExcels]

@ToniZ2010
The Big Yellow Dot that you see is a "Cursor Effect" that I apply when editing these videos. I use a software program called Camtasia Studio to record, edit and produce my video tutorials.

Danny Rocks
The Company Rocks

[gibrain]

Can we name a worksheet after several cells rather than just one single cell?

[paulk]

Thank you for making this video!
It's already helping me a lot.

Now,  to apply this from the third tab on.

I changed the row: For each ws in Thisworkbook.worksheets
to: For sh = 3 To Sheets.Count

Unfortunately, it doesn't work.

Who could help me out here?

[ryanmarsolan]

Thank you for your service and your response. I submitted a request via email. Would appreciate your input.

Ryan