Change Multiple Sheets Names automatically based on cells

[jshrtader]

I have a workbook with multiple sheet names with cryptic sheet names. In order to track how it is used each time, I will go to a central sheet ("Preferences" in the attached file) and type in the customer names in Column B. Even with this, I spend too much time constantly having to refer back to my Preferences worksheet.

Is there a way to have the entries in Column B automatically change from the entries defaulted in Column A? This would save a lot of time and make it more user friendly!

Thanks in advance for your insight!

[lucky027]

could you try this code
It changes all sheets name and if you run it a second time that return the old name.
Hope that help you
Have a nice day...

Code: Select all

Sub change_name_sheet()
Dim lastrow
lastrow = Range("A" & Rows.Count).End(xlUp).Row
If Sheets(2).Name = Range("A3") Then
For I = 2 To lastrow
Sheets("" & Range("A" & I) & "").Name = Range("B" & I)
Next
Else
For I = 3 To lastrow
Sheets("" & Range("B" & I) & "").Name = Range("A" & I)
Next
End If
End Sub

[jshrtader]

Thank you Luck027 for your fast reply!!!!

I am embarrassed to say that I am pretty good with basic and some advanced Excel functions, but I have never used Macros or VBA. Is the"code" that you refer to a Macro or VBA?

I apologize in advance that it will take me some time to learn how to make this work. Once I do, I will update the post and let you know if it worked!!!

[charles]

jshrtader,

Its VBA is an acronym for Excel and VB6 (stand alone and works with Excel and Access I think). Macro is just a name for the VBA code.

[lucky027]

don't apologize, I try to explain you
VBA is a application to show the language basic with a editor in Excel (and more Microsoft applications)
You can find this editor when you type CTRL + F11 or in command bar TOOLS=>MACRO=> VIsual Basic Editor

You have a lot of possiblities to install the macro/code to make that you want
- in thisworbook, sheet, module code and class module code
You can use userform to show form to the user with code inside.

In my case the code that I provide you is a simple macro.
You can copy it in a module or this workbook code and run it with CTRL+F8 or create a button and assign the macro on it.

I hope that I'm clear in my purpose.
Sorry for my bad english I'm french and not a expert of this language.
Have a nice day

[Sisyphus]

Hi,

I wonder if you got lucky027's macro to work for you.
If not, I wonder if you really need to change the sheet names. You could create hyperlinks on your Preferences worksheet. When you click on the extended name the associated worksheet opens up - whatever its name. Wouldn't that save you even more time?

Happy New Year!