Naming Sheets by Date

dbattles22

New Member
Joined
Nov 3, 2015
Messages
5
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!!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.

Code:
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
 
Upvote 0
No, I was just explaining what I did. You can paste the code into your workbook.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top