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

 

[GhostViper]

The only reason I ask is that the file is created from the start of the work week of the new year (Monday) and has to end at the end of the work week (Sunday). So, for example, the first full work week of 2020 starts on 01/06/2020, which is what the first date of the file needs to be (01/01/2020-01/05/2020 is included in the 2019 file) and it will end on 01/03/2021. Each year it will be different, so the script will need to be "adjusted" yearly to account for the first work week. I could technically delete the tabs that are not needed, but then I have to start changing formulas to account for the missing tabs because each tab relies on the previous tab, except the first tab. This file will be a master file which will be updated yearly, locked and protected, and then copied and transferred into a location where daily operation employees will then access it.

 

[GWteB]

@GhostViper Question: is de cell (on each sheet) date formatted?
(right click cell > cell properties)

 

[GhostViper]

@GWteB The date is formatted (mm-dd-yyyy), but is then converted to text to prevent excel from trying to put "/" (excel still stores the date as m/d/yy) in the date which would prevent the date from being input as a tab name.

 

[GWteB]

Sorry I was not clear. If you change the cell format to Standard, does a number appear (like 43832 for todays date)?

 

[GWteB]

If so, you can use this code. Note the first line, it can be modified ...

Public Sub ChangeSheetNames()

    Const cDate_CellAddress     As String = "S1"

    Dim oSht        As Worksheet
    Dim dtDate      As Date
    Dim strDay      As String
    Dim strDate     As String

    ' clear all names to prevent duplications
    For Each oSht In ThisWorkbook.Worksheets
        With oSht
            .Name = .CodeName
        End With
    Next oSht
    ' rename all sheets based on date in specific cell
    For Each oSht In ThisWorkbook.Worksheets
        With oSht
            dtDate = .Range(cDate_CellAddress).Value
            strDate = Format(dtDate, "mm-dd-yyyy")
            strDay = WeekdayName(Weekday(dtDate), , vbSunday)
            .Name = strDate & " " & strDay
        End With
    Next oSht
    Set oSht = Nothing
End Sub

 

[GhostViper]

Thank you everyone for the input and the help. I hope everyone has a great day.

Thanks again,
GhostViper

 

[GWteB]

You're welcome. Thanks for your reply. Hopefully you sorted it out.

 

[My Aswer Is This]

OK. I'm confused. We have several posters joining in to this thread. So I assume you have all this sorted out and you need no more of my help. Take care glad you have things sorted out.