Automatically Change Sheet Tab Name With Date in Cell

Hello! I am trying to automate naming all my tabs with the date entered into cell A2 in every worksheet, but with a different date format. The date format of the A2 is [$-F800]dddd, mmmm dd, yyyy (or Friday, April 01, 2011). But I want the tab to be named "Apr-01 Fri". Here is what I tried: to convert the formats, I decided to use the next located cell B2 with a different date format, white-on-white text and referencing it to =A2 with the code below. It works perfectly fine with word-strings in B2, but when entering a date, it gives me my code's error of invalid sheet name. My guess is that the date-format should be converted within code, not in the cell. Thank you for any help!

Please Login or Register  to view this content.

Hi Chris,

Could you please edit the above post by clicking on EDIT and then "Go Advanced", select the Code and then click on the "#" Icon above the message area. Then Save. This will follow the forum rules and make your code much more readable for us.

After you do the above.

Why do you have your code behind the worksheets instead of in a "Module"?
In a module you can grab or set the activesheet name using

Please Login or Register  to view this content.

Hope this helps.

Did you fix your code above?

Hi,

Here is an example workbook, with code in a Module that will rename the worksheets to what is in cell B2 on each sheet. See if this helps.

Code behind a worksheet (instead in a module) is there to trap worksheet events on that individual worksheet. It is generally better to keep all (non event) code in a Module.

Read this http://www.cpearson.com/excel/codemods.htm about the topic.