Automatically Change Worksheet Names Based On Cell Values || Rename Excel sheet with cell contents

In some of our Excel reports, we may want to name the sheets automatically according to the names on each sheet without manually renaming them.

So, in this tutorial, let us learn how we can automatically change Worksheet names based on Cell values using a small macro.

Best Laptops to use for better speed:

In this example, I am considering a sample report of 6 states in 6 sheets and let us rename these sheets with that of States names from cell B2 of each sheet.

➡️ VBA code:

Sub RenameSheet()

Dim rs As Worksheet

For Each rs In Sheets
rs.Name = rs.Range("B2")
Next rs

End Sub

And paste in this new module.

Step3:
Close this VBA window and go back to your excel workbook

Step4:
Go to view tab and under macros, click on view macros

Step5:
After selecting the renamesheet macro, click on run

Step6:
Now, you can see that the name of each sheet is renamed in accordance with the cell name on each sheet.

That's it friends. I hope this is very use full for those who spend lot of time on Excel.

Our Recommendations
***************************************************************

If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.

***********************************************

⚡️Tools for YouTube vlogging:
***********************************************

***********************************************

⚡️You Can Connect with Me at:
***********************************************

#dptutorials #renameworksheets #Exceltraining #ExcelTricks #ExcelTips #ExcelFreeTraining #ExcelFreeLearning

⚡️Tags: -
excel formulas in English, excel in English, excel tutorial in English,ms excel in English,ms excel tutorial in English, learn excel in English,vlookup in excel in English, learn ms excel in English, excel training, excel tutorial, Microsoft Excel 2007, learn excel,tutorial excel, ms excel tutorial, excel tutorials,ms excel 2007,Microsoft Excel training,learn excel online,learning excel,free excel training,online excel training,advanced excel tutorial,excel tutorial, excel formulas and functions, excel formulas, excel tutorial in Hindi, excel formulas and functions in Hindi, excel tricks, excel in Hindi, excel shortcut keys, excel vlookup, excel formulas in Hindi, excel for beginners, excel for accounting, excel formulas and functions tutorial, Excel Sum Formula, Sum Formula series, excel attendance sheet, excel salary sheet, excel stock maintain, excel data entry, advanced excel tutorial,excel formulas,excel tutorial,vlookup excel,excel accounting,excel for beginners,excel shortcut keys,excel sum formula,excel training,excel training online,excel tricks,free excel training,learn excel,learn excel online,microsoft excel training,attendance sheet in excel,excel data entry,excel formulas and functions,microsoft excel 2007, ms excel tutorial, excel formulas, and functions in hindi.

[jdsteel]

I found this extremely helpful thankyou

[bda]

Its working.. For those having "Name already taken issues" - its because the cell specified in the code have the same values in all the worksheets. You need to have a unique value in the cell(the cell for naming your worksheet tab) or you need to change the cell address in the code itself

[heyitsjujuu]

I had to use sheets for monthly data. Helps a lot the automation!!

[albertwilson]

for me this line says error
"rs.Name = rs.Range("B2")"

[nairobi]

Cool tip. Thanks. Just one thing I found out: If the reference cell is empty I get Run-time error `1004´: Method `Name`of object`_Worksheet`failed. To avoid it add an if statement: So the code looks like:

Sub RenameSheet ()
Dim rs As Worksheet
For Each rs in Sheets
If rs.Range("A1")<>"" Then
rs.Name=rs.Range("A1")
End If
Next rs
End sub
I then created a button and assigned the macro to it.
Cheers

[baden]

I want tabs to automatically be named as soon as I create it. I replace tabs with other tabs often and want the new tabs to automatically be given the name of a cell.
I do not want to have to 'rightclick-> View Code -> insert Code etc.' every time I replace a tab.

[arturasjankauskas]

Thank you works perfect. How to edit macro to contain sheets name from two cells?

[Slyrouth]

This is so close to what I'm trying to do.

[Sami-Ur-Rehman-Blogger]

Thank you for sharing such a useful task. 👍
Sir, i have a query, How to change/move specific cell value on the base of drop-down list.

[hajiso]

not applicable in my excel sheet error as below
Run-time error'424': object required, please reply

[Zrzmovies]

It works only if the name is letters but when you have numbers as well it won't work. How can change formula so it will show date as sheet name? Thanks

[SalAbraham]

But what if you want to rename some specific sheets based on cell value if they contain text ?

[marceljunioregondi]

This is nice but would be better if you broke down the VBA Code so that we don't have to copy your code but can write our own.

[savvy_me]

Hello brother i want the people's names to be automatically entered in the right cell when i enter their number in the left cell so i don't have to type the name and number every time

[yosibest]

Thanks for the video, very useful, is there a macro to be used if the contents of the cell (in this case B2) contains a formula that refers to a summary worksheet??

[jamalakeembasdeo]

i want to rename all my worksheets except the 1st one how do i code that

[manoshikadamunugolla]

Thanks for the video. It was really helpful. Could you let me know how can I rename the "Workbook" according to the cell value?

[dlrjgys]

Can you perform this in Google Sheet? Thanks in advance.

[donnawinski]

How do you get this macro to run automatically when the cell data changes? Thank you.

[ezequiasrocha]

I would like to trigger every time a tab is renamed looking for the previous name and update it to the new one.