Dynamic Worksheet Tab Names, driven by cell value from another worksheet.

[Doc Science]

I need to dynamically rename the Worksheet names, from a cell value in another worksheet.

The name’s source is from: Sheet4
The worksheets to change are Sheet1, Sheet2, and Sheet3

The value of the name, in it’s cell is a date, and is formatted as m/d/yyyy (10/1/2014)

The worksheet name needs to be in this format ddd mm-dd (Sat 10-01)

Sheet1’s name will be located at =TEXT(Sheet4!A1,"ddd mm-dd")
Sheet2’s name will be located at =TEXT(Sheet4!A2,"ddd mm-dd")
Sheet3’s name will be located at =TEXT(Sheet4!A3,"ddd mm-dd")

I have a Macro that must be ran after information is pasted into Sheet4.
So I have an opportunity to embed this “Renaming” solution into that macro if necessary.

---

I know for instance, the following macro will change the tab name to the contents of A1 from an active worksheet.

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

 

[Snakehips]

Doc Science,

Not sure if you are doing all three at same hit but have assumed yes?

You could perhaps do it by sheet's index but if sheets are named as Sheet1 Sheet2 etc then....

Sub ReNameTab()
For i = 1 To 3
Sheets("Sheet" & i).Name = Format(Sheets("Sheet4").Range("A" & i), "ddd mm-dd")
Next i
End Sub

Hope that helps.

 

[JoeMo]

Here's some code for sheet4 that will automatically update the names of sheets 1-3 (assuming those are the code names for those sheets) whenever you change a date in the range A1:A3 on sheet4.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
    Sheet1.Name = Format(Range("A1"), "ddd mm-dd")
    Sheet2.Name = Format(Range("A2"), "ddd mm-dd")
    Sheet3.Name = Format(Range("A3"), "ddd mm-dd")
End If
End Sub

If you already have the dates you want in sheet4, you can start the process by selecting any cell in A1:A3 on sheet4, press the F2 key and hit Enter.