Rename All Sheets From A List... In One Step
HTML-код
- Опубликовано: 29 сен 2019
- There are many ways of renaming sheets in Excel.
However if you have a large number of sheets you can rename all of them in one step following a list in the worksheet itself
In this tutorial I show you how to name any number of Sheets in one step by using a List in the worksheet.
You can Download the Exercise File and the code by clicking on the link here below.
www.amazon.ca/clouddrive/shar...
You can read the steps on my blogsite www.See-How.ca
To book a corporate Training visit our website www.OfficeInstructor.ca
For Microsoft Office Specialist Certifications - MOS - and to book an exam visit www.OfficeInstructor.com
What a great clear video! You explain clearly what you do and what it means. That is what is helping many people to understand!
THIS WAS AWESOMEEEEEEEEEEEEEEEEEEEEEEEEEEE! I am so glad I found this video Thank you! I was able to follow your instructions and I renamed 23 tabs in a matter of minutes. Much appreciated! God Bless you!!
No extraneous chat. Short and directly to the point. Well done.
Thank you for the motivating comment
Fantastically clear explanation and helpful visuals that compliment it perfectly. Really helpful for a novice VBA user like me
Thanks Nabil for this video. I am beginner in vba and your videos are helping me to learn the concepts slowly. Kudos, keep going 🙌
Thank you very much for an easy to follow video. Very thankful to have found your channel. GREAT STUFF!!
You're very welcome Sheri
I am from bangladesh. I watch many vedio for this problem.. and finally I can say it's simple and very easier then others. Thanks a lot sir
Thanks - Enjoy your clear explanation of all the details
Stumbled on your channel. Love it, straight to the point useful videos. Thanks BTW, subscribed after the 3rd video.
Thank you Gregory
An excellent video, explained with much clarity and passion, thanks
Great concise and step by steps instructions. Thank you very much.
Glad you liked it
Amazing, very helpful. Thank you Nabil!
Thank you Luciano
Thank you Nabil for this amazing tutorial..
Thanks for sharing..👍
My pleasure 😊
As a specialist teacher, I have to deal with multiple classes. I used to name each sheet as per your introduction. Thank you for this valuable and short method. Since I use all the sheets to insert students names, I named Sheet 1 as YEAR LEVELS created number of sheets according to the number of year levels. Thanking you again.
I am glad Abdulkader my video helped you in your work situation
great thank you so much, i helped me a lot with a workbook with more than 200 sheets :)
Glad I was able to help...
Absolutely AMAZING! Thank you :)
Great instructions. Thanks!
Hi Nabil,
Thank you for posting this extremely useful video.
Your VBA explanation was clear & concise.
You are most welcome
You are most welcome
Worked for me - excellent, thank you.
Great to hear!
THAT IS perfect... thanks a lot man.
You are great man!! it was extremely helpfull and thanks for quick codding tutorial.
You're welcome!
Thank you so much this was helpful
I do not know abc of VBA. But by this video I am able to rename lot of excel sheets at a single click! Thanks
Congratulations on creating your first code
Awesome way of explaining
Thank you
Very helpful. Thank you!
Glad it was helpful!
Thank you very much, Sir!
Amazing, you saved my lots of effort hurs.
Thank you for sharing this, i have been looking to use this function for a week. It works great
Dear many thanks
Amazing work.
Thank you Shaju Paul
when using F8 on the fourth row "Sheet" is highlighted as an error. compiler error message is "variable not defined"
so when defined sheet as worksheets, it worked thanks for the amazing tutorial.
Glad it worked
interesting and timesaving video thanks a lot.....
You are welcome
After a few glitches I got it to work.
I was trying to put days of the month, which excel automatically turns into a date, that cannot be a sheet name (no / allowed ).
A couple of extra steps fixing the list and changing it to text and bingo 35 sheets done and rolled over for another month!
I think I watched another of your videos for making a totals page from a range of sheets, so there was a "first", "Last", "totals" and "rename sheets"
well done!
Thanks so much!!!
Great work, can you do the VBA for sheet name AND add sheets depending on how many rows contain data?
Thank you sir from india
Amazing!
Amazing video
Thank you bro
Thank you so match, Sir.
Always welcome
What a fantastic video! Could you provide me and advice how to limit to 30 caracters? I have a list taken from an other cell and it’s more than 30 ( limit for sheet name)
Thanks sir for file & u r amazing sir
Thank you for the encouragement
This an excellent video, Nabil. I always wondered how to switch from one sheet to the next in order to change the names, and this is a fabulous workaround! As a language teacher, VBA makes me a bit nervous because I'm always worried about spacing, capitalization, and syntax - the same as I would while teaching composition. How do I learn the rules for VBA spacing, capitalization, indentation, etc? Keep up the fantastic work, Nabeel!
Thank you for the nice comment... Did you Subscribe to my Channel? The Best Is Yet To Come
Thank you sir, really it is great class for me.
Sir, what steps in same situation in Google spreadsheets?
Please guide me.
Thank you
Nice one
Wow nice trick thanks allot
Glad you liked it
This is a great job. I do not use VB, but the way you show it and the steps are clear to anyone. Thanks a lot for this video. I am always dealing with this issue, and finally found the answer. However, how to deal with multiple excel files for which, let's say, we need to rename sheets using the same group of names? Thanks.
Performing an action on multiple files requires looping over workbooks
Amazing
it is great, sir
Excellent
Thank you so much 😀
This is an AMAZING VIDEO! I spent so much time on other videos and I would always get an error or a bug and could never figure it out. Thank you so much! I want to ask, how do I get the sheets to start renaming beginning from the second sheet, as I don't want Sheet 1 (The list of names) to be renamed as the first name on my list?
thank for you
Thank you too
Sir what if we want "name" to be the date... I. E my workbook is named "july, 2021" and want to name sheets by date for every single day. Pls guide
Great
Super and easy
Thank you 🙂
Good day, Nabil, I am trying to follow along but I do not have the same Excel version you have, which I believe is a problem. Our tool bars are different and I also do not have access to visual basic editor. Is there another way to range the a1 column and sheets? Thanks
Great video, but i wondering if I want the sheet where the list of names don't to be named with the code how to do this?
Very nyc
Thank you
good 👍👍
Thanks
hi works great but if you want to update say 5 name out of 100 as soon as the macro hits a sheet name which is the same as it is trying to change it to it stops and errors out.. how can I fix this
Thanks very useful , quick question
once all the sheets have been renamed , can I come back and delete the macro if a) I dont want to rename more sheets in future b) I want to retain a macro free workbook ?
Yes, you can, Vinod
Hi everything work perfect , but I want to star putting name on the second sheet . What can I change in the coding ?
Hi , would like to ask how to save can use this marco for everyday . ?
Thanks for the lesson, however I am trying to use this format in a workbook in which the naming sheet is the second sheet in the workbook and will only rename sheets after the naming sheet. Can you help me?
Hi
Assuming that the first sheet is named "Nabil" and we want to start renaming from the second sheet having the list of names, then I will add a conditional statement that evaluates the sheet name first before renaming, as follows:
Sub RenameSheets()
Dim i As Integer
i = 1
For Each Sheet In Sheets
If Sheet.Name "Nabil" Then
If Cells(i, 1) "" Then
Sheet.Name = Cells(i, 1).Value
i = i + 1
End If
End If
Next Sheet
End Sub
Good Luck
i want to start the renaming on the next sheet, not on the same sheet. What should I modify in the code? Thanks
I am copying multiple sheets in one excel and want rename it.. but due some restrictions I am not supposed to have macro in that main excel sheet.. is there any way that I can have this macro in another workbook and it will rename the sheets in the main excel sheet automatically...
What if I want to hyperlink at the same time? Thanks the video it's very helpful
You can... watch my other videos about Extracting a List of Sheet names and hyperlinking them
THANK YOU NABIL AMAZING VEDIOS I WANT TO CONNECT WITH YOU
I'm glad you enjoyed my tutorials.
You can connect with me by clicking on any of the website links in my tutorials where you'll find my contacts
I've done exactly what was showed in the video which works perfectly however the other formulas I had in my main sheet now shows BLOCKED what does this mean and how do I get around it?
How do you indent your vb code is there a short cut key!! you use. Thanks
Just put your cursor at the beginning of the line and hit TAB as many times as you want
What about if you are wanting to do a Date format? I'm failing with this. If I put in alpha characters only it works, but nothing with a date
I have 12 cells I want to name 12 of the sheets (not all sheets just 12 in the middle of 24. and Cells are spread over 4*3 layout. Cells H5:K8) any thoughts?
What if present name and changing name of some sheets are same. I am facing this problem getting debug message that try other method present name is same
how do i avoid name is already taken error? I want the name to be replaced even if the sheet name is already the required name.
i get an error if the cells contain anything other than letter text. i.e a date or number
Sir, incase u have to change name is automatically rename change or not??
How to change one or moreTabs name or worksheets name in upper Case by vba? my worksheets has 100 nos
Can I rename worksheet name as workbook?? For single sheet only??
I have number of files in Excel to rename??
How can we make it so that it skips sheets with the name already taken and not give an error
sir suppose i have list 15 name of sheet so please make vba code to creat sheet as per list if possible
I don't know if you're still checking comments. I put your code into my VBA exactly as you had it, but when I click 'Run' nothing happens.
may be macros are disabled
if i want to skip the first sheet and start renaming from the second sheet, how to do it?
You need to wrap the looping in a conditional statement
@@Officeinstructor i don't get it
Sir it is unable run in excel 2013 , what we have to do? Plz help
It keeps throwing an error on "Sheet.Name = Cells(i, 1).Value"
Is it working on Google sheet?
did not work for me. I do not want to rename the tab that has the naming structure or the tab that has the summed up data but instead rename the remaining tabs. for example i have the first tab that will have the collected data represented and then the next subsequent tabs broken down by the day of the month for the entire month.
This only worked for the first 5 names, I have 45 names. My first name starts at A4, my i value is 4, right?
any code needs customization according to the work situation. I am just explaining a concept
how can i rename a group of 10 sheets (middle) in a workbook
You need to customize the code
@@Officeinstructor I came across the same situation. I have close to 200 sheets, and I only want to rename the mid 20 or so sheets. How do I customize the code to get this working?
Can you see me the code for this? Many thanks
my first tab changed to 9176?? why?
I have followed your video several times and have even copied your code for my workbook but it will not work. I have a workbook that is called "Mixed poker rooster" that keeps track of poker players for our bowling league. It consists of a master sheet that tallies weekly deposits. The sheets are named for the day of the week for 35 weeks. IE master, 02-sep-2019, 09-sep-2019, etc. for thirty - five weeks.
the statement "Sheet.Name = Cells(i, 1).Value" never gets run.
Here is my routine;
Sub RenameSheets()
Dim i As Integer
i = 1
For Each Sheet In Sheets
If Cells(i, 1) "" Then
Sheet.Name = Cells(i, 1).Value
i = i + 1
End If
Next Sheet
End Sub
Why "dim i" and not "dim Sheet"? Seems sloppy. I would prefer to use Option Explicit and declare both variables
If you are using Option explicit add this Dim Sheet As Object
Dear many thanks
Amazing!
Thank you! Cheers!