Naming Sheets by Date

[dbattles22]

I am having trouble figuring out a VBA code to name all the sheets in my worksheet dates. I want to be able to change the dates in my main worksheet and automatically rename each sheet based off the dates I have entered. Please help!!

 

[lrobbo314]

Welcome to the forum. I created a sample worksheet with the dates on a Master sheet, named "Master". The dates go from A2 down. It formats the dates to 11-3-2015, because you can't use / in sheet names. The code will name the sheets in the workbook going down the list you have in column A. You might need to adjust your ranges.

Sub tdName()
Dim rDates      As Range
Dim cel         As Range
Dim Master      As Worksheet

Set Master = Sheets("Master")
Set rDates = Master.Range("A2", Master.Range("A" & Rows.Count).End(xlUp))

If ActiveWorkbook.Sheets.Count - 1 < rDates.Cells.Count Then
    MsgBox "There are more dates than sheets!", vbExclamation, "Error"
Else
    For i = 1 To rDates.Cells.Count
        Sheets(i + 1).Name = Format(rDates.Cells(i).Value, "m-d-yyyy")
    Next i
End If

End Sub

 

[dbattles22]

Is the sample worksheet attached on here somewhere for me to look at? I am new to this.

 

[lrobbo314]

No, I was just explaining what I did. You can paste the code into your workbook.

 

[dbattles22]

okay I am really new to vba codes. Here is my scenario. I have a worksheet that has a main sheet named Weekly Chart and all the other sheets named Dates. Within the weekly chart I have dates that I change each time I send this spreadsheet to a new person. I am trying to figure out how all the other sheets will update the new sheet name to the new dates when I update them on the weekly chart.

 

[lrobbo314]

The way I understand what you want to do is that you have, let's say 10 sheets in your workbook. The sheet with the dates, "Weekly Chart", has a column that has 9 dates in it. These numbers are just examples, I don't know how many sheets you actually have. The code would look at the dates, cell by cell, in "Weekly Chart" and sequentially name sheets 2-9 the dates in that column.

So, if the column looks like

Dates
11/1/2015
11/2/2015
11/3/2015
etc.

Then, in this example, Sheet2 would be named, 11-1-2015, Sheet3 is 11-2-2015, and so on.

I have updated the code a little bit with some comments.

Let me know if I understand what you are looking for correctly.

Sub tdName()
Dim rDates      As Range
Dim cel         As Range
Dim Master      As Worksheet

Set Master = Sheets("Weekly Chart")
Set rDates = Master.Range("A2", Master.Range("A" & Rows.Count).End(xlUp)) 'I assumed your dates were in column A, starting in A2. Change if necessary.

If ActiveWorkbook.Sheets.Count - 1 < rDates.Cells.Count Then 'If there are more dates than sheets, it will give an error and nothing will happen.
    MsgBox "There are more dates than sheets!", vbExclamation, "Error"
Else
    For i = 1 To rDates.Cells.Count 'Loops through dates column.
        Sheets(i + 1).Name = Format(rDates.Cells(i).Value, "m-d-yyyy") Names sheets the date values from column A on "Weekly Chart"
    Next i
End If

End Sub

 

[dbattles22]

Thank you! One dumb question since I am so new to VBA codes, where all does this code go? on every sheet? just the Weekly chart sheet?. I previously had a code that worked but it was for active sheets and it didn't update when I changed the date.

 

[dbattles22]

could you possibly e-mail me an example worksheet?? please