Using VBA to Make Sheet Name equal to Cell Data

[GhostViper]

Good morning Everyone,

I am trying to make my tab/sheet names equal to the cell values. I am using the formula provided by this page:

Dynamic Worksheet Tab Names (Microsoft Excel)

Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but it the task is rather easy as you discover in this tip. (Tips.Net)

excel.tips.net

This formula was created to pull values from a single cell. I need to pull values from two different cells and combine them into the sheet name. For example, cell A1 is "01-02-2020" and cell A2 is "Thursday". I would like the tab/sheet name to be "01-02-2020 Thursday". I have found other formulas that were able to do it, but they do not automatically update like the one above. I am not all that familiar with coding so changing coding is outside of my skill range.

I mentioned above that the formula automatically updates, but does not update if cell A1 has a formula in it that links to another excel tab. For example, cell A1 on tab 1 is "01-02-2020", and cell A1 on tab 2 is "='tab 1'!A1". Tab 1 then updates, but the rest of the tabs do not. I can go to each tab and click somewhere on the tab to make it update, but this really slows down the process. I found a thread saying to change to first line of code to "Private Sub Worksheet_Calculate()". Would this work? And would the worksheet notice a large delay or calculating time? The workbook has a tab for each day of operation, and each sheet needs to be named after the date and day of the week. Updating 365 tab names is quite tedious, so an automated way to do this would be great. But, new data is input into the document throughout the day, so a long calculating time or delay for inputting data would cause problems. The workbook already contains formulas which cause a noticeable delay (2-4 second delay between each data entry).

If there is a version of the code above that could be put into "ThisWorkbook" module with the calculate code mentioned above, that would also work. I could update the file once every 12 months and then simply remove the code to prevent any delay and calculating time.

Any help would be greatly appreciated.

Thank you in advance,
GhostViper

 

[PCL]

Are all sheets concerned to be renamed using cells A1 and A2 ???
What about a code launched every time and only when the file is opening or closing ??

 

[GhostViper]

Well, it will actually be cell S1 and S2 or something of the sorts. It has to be off to the side of the data entry areas and out of the print area because the date has to be converted to text. Otherwise, excel panics because "/" is used and that cannot be put into a tab name. But, it will be the same two cells on all sheets throughout the document.

 

[My Aswer Is This]

Why not name all the sheets using a script that would not rely on a cell value.
This script will name all sheets the way you asked for.

My script here only does the first 7 sheets just to show you how it works.
If this works for you change 7 to 365

Sub Sheet_Names()
'Modified  1/2/2020  11:19:09 AM  EST
Dim i As Long
For i = 1 To 7
    Sheets(i).Name = Format(DateAdd("d", i - 1, "1/1/2020"), "MM-DD-YYYY DDDD")
Next
End Sub

 

[My Aswer Is This]

Actually for the year 2020 you would need 366
The year 2020 is a leap year which has 366 days

 

[PCL]

If this works for you change 7 to 365

Why not to do it for all sheets ...?

 

[GhostViper]

For the code above, does that go into each individual excel tab or into the "ThisWorkbook" module? I tried placing it into the "ThisWorkbook" and there were no changes to the tab names.

 

[My Aswer Is This]

You could do this for all sheets like this:

Sub Sheet_Names()
'Modified  1/2/2020  12:12:55 PM  EST
Dim i As Long

For i = 1 To Sheets.Count
    Sheets(i).Name = Format(DateAdd("d", i - 1, "1/1/2020"), "MM-DD-YYYY DDDD")
Next
End Sub

 

[My Aswer Is This]

For the code above, does that go into each individual excel tab or into the "ThisWorkbook" module? I tried placing it into the "ThisWorkbook" and there were no changes to the tab names.

This is a module script. Put the script in a button and press the button and the script runs.
It does not go in this workbook.

You will only need to run the script once.

 

[GhostViper]

Okay, I think I have figured out the button and script. When the date is "1/1/2020", the button works as intended. If I change the date to say "1/2/2020", I get an error "400". I do not know what this means. If I change the date to "1/2/2021", the button works again. Is there a reason for this?