Using VBA to rename worksheet based on cell value

Hello Maciec,

A simple formula and a slight adjustment to your code may be all you need. But first, for the sake of the exercise, lets make some assumptions:-

1) Cell A1 in the "Index" sheet (I'll refer to it as Sheet2) is the cell where you make the name changes.
2) Cell B12 in Sheet1 is the cell you have allocated for the code to reference for the sheet name changes.
3) The Active Sheet is Sheet2.

In Sheet1 B12, type the following formula:-

Please Login or Register  to view this content.

Don't use cell B12 for any other purpose as typing anything else in it will delete the formula.

In the Sheet2 module, place the following code:-

Please Login or Register  to view this content.

You'll probably notice the minor changes to your code and you'll notice that we are referencing Sheet1 from Sheet2.

To implement the code, right click on the Sheet2 ("Index") tab and select "view code" from the menu that appears. In the big white field that then appears, paste the above code.

Now, every time that you change the name in cell A1 Sheet2, the formula in Sheet1 B12 will take that name across to Sheet1 B12 and, from there, the code will do the rest.

In a situation as yours, it would be best to refer to each sheet by its sheet code in any VBA code as I have done above. In the Project Explorer frame to the left of the main code field, you'll see all your sheets listed with the sheet code (Sheet1, Sheet2..etc.) with the given sheet name beside it. This is a very secure way of coding so as sheet names may change, the reference won't and you won't have to adjust the code each time a sheet name changes.

I've attached my test work book for you to play with.

I hope that this helps.

Cheerio,
vcoolio.

Thanks vcoolio! I will give this a try and let you know if it works for me.

It works, but only for one sheet. I have 20 other sheets that I need this to work for. How can I do this?

Hello Maciec,

Aha. Many sheets!

A slight change of plan then.

In your "Index" sheet, I'll assume that Column A, not just cell A1, will be used for the name changing purposes. As you need to transfer those names as required to cell B12 of each sheet, then use the same formula in B12 for each sheet but referencing consecutive cells in Column A of the "Index" sheet.
So, for Sheet1, the formula in B12 should be :=

=Index!A1

for Sheet3 (Sheet2 is the "Index" sheet):-

=Index!A2

for Sheet4:-

=Index!A3

etc., etc. etc.....

An easier way to do this for each sheet:-

In B12 of Sheet1, place the equals (=) symbol then go to the "Index" sheet and click on cell A1 (it will high-light with "marching ants) and hit Enter. The formula will then appear in Sheet1, B12. Do the same for Sheet3, Sheet4 etc..............

Now, place the following code in the "Index" sheet module as previously explained:-

Please Login or Register  to view this content.

Thus, each time a name is changed in any cell in Column A of the "Index" sheet, the relevant tab will be re-named. I've attached an updated test work book to show you how it works. There are a few notes for you in the test work book.

Thanks again vcoolio! But I seem to be having trouble with the execution. I keep getting the Run-time error '1004'. I've gone into the settings and checked the box to allow Visual Basic Macros, but it still doesn't work. I have attached an image of the VBA window that comes up when I click on "debug." There's also a copy of the workbook I'm trying to work with.

RunTime1004Result.png

Hello Maciec,

I see that you've changed the formula to suit your needs. Very good!

The error shown in the screen shot is brought about by the fact that you don't have a sheet named "Index". So modify the code as follows:-

Please Login or Register  to view this content.

and you should be back on track. I've tested it in your work book and it now works fine.

Good luck! Come back if anything else crops up.

Cheerio,
vcoolio.

I probably should have seen that. Thank you so much for your help! Everything is working beautifully!

Hello Maciec,

You're welcome. Glad that I was able to help.

Cheerio,
vcoolio.

Originally Posted by vcoolio

Hello Maciec,

Aha. Many sheets!

A slight change of plan then.

In your "Index" sheet, I'll assume that Column A, not just cell A1, will be used for the name changing purposes. As you need to transfer those names as required to cell B12 of each sheet, then use the same formula in B12 for each sheet but referencing consecutive cells in Column A of the "Index" sheet.
So, for Sheet1, the formula in B12 should be :=

=Index!A1

for Sheet3 (Sheet2 is the "Index" sheet):-

=Index!A2

for Sheet4:-

=Index!A3

etc., etc. etc.....

An easier way to do this for each sheet:-

In B12 of Sheet1, place the equals (=) symbol then go to the "Index" sheet and click on cell A1 (it will high-light with "marching ants) and hit Enter. The formula will then appear in Sheet1, B12. Do the same for Sheet3, Sheet4 etc..............

Now, place the following code in the "Index" sheet module as previously explained:-

Please Login or Register  to view this content.

Thus, each time a name is changed in any cell in Column A of the "Index" sheet, the relevant tab will be re-named. I've attached an updated test work book to show you how it works. There are a few notes for you in the test work book.

Hi Vcoolio,

Is it possible to just name worksheets based directly on just the Index sheet not on the B12 cell inside that worksheets?

Thanks

Hello Dedark05,

If you are re-naming all sheets from a list in an "Index" sheet (say Column A), you could try the following code:-

Please Login or Register  to view this content.

If its not quite what you would like then please start a new thread properly explaining what you would like to do. Someone should be able to help you from there.

This thread is Maciec's thread and has been marked as solved.

Cheerio,
vcoolio.

Exactly as what I need. Thanks, Vcoolio!

Hello Dedark,

You're welcome. Remember to start a new thread should you have any further queries regards same.

Cheerio,
vcoolio.