Auto rename sheets 1-7 with weekdays and dates

**jimpr** · 08-01-2013, 02:36 PM

Hello everyone. I am new to VB and this forum. TIA for any assistance anyone can provide.

I am looking for a macro/VBA code that will rename sheets 1-7 as "Sunday MMDDYY" thru "Saturday MMDDYY", respectively. Ideally, it would trigger based on the change of a single cell in sheet10. The sheet10 B1 cell contains a text date value that will be edited by the spreadsheet user at the end of each week to update dates in the sheet names and spreadsheet calculations for the next weeks information. I have everything done except the tab auto rename which I am struggling with, not knowing VB very well.

Alternately, all A1 cells of sheet1 - sheet7 have a date but it is a referenced value from sheet10 cell B1 so the sub function I have found does not work since those cell values are not text.

Any help would be greatly appreciated. thanks much.

**blue.chio** · 08-02-2013, 04:35 AM

Hi,
welcome to the forum!
If you have a date in every cell A1 (Sheet1 to Sheet7) then just for a test you can try it on a test file.
add it to worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b1")) Is Nothing Then
Dim dat As Date
dat1 = Sheet1.Range("A1").Value 'date
dat2 = Sheet2.Range("A1").Value
dat3 = Sheet3.Range("A1").Value
dat4 = Sheet4.Range("A1").Value
dat5 = Sheet5.Range("A1").Value
dat6 = Sheet6.Range("A1").Value
dat7 = Sheet7.Range("A1").Value

Dim strName As String
strName1 = Format(dat1, "dddd dd-mmm-yyyy")
strName2 = Format(dat2, "dddd dd-mmm-yyyy")
strName3 = Format(dat3, "dddd dd-mmm-yyyy")
strName4 = Format(dat4, "dddd dd-mmm-yyyy")
strName5 = Format(dat5, "dddd dd-mmm-yyyy")
strName6 = Format(dat6, "dddd dd-mmm-yyyy")
strName7 = Format(dat7, "dddd dd-mmm-yyyy")

Sheet1.Name = strName1
Sheet2.Name = strName2
Sheet3.Name = strName3
Sheet4.Name = strName4
Sheet5.Name = strName5
Sheet6.Name = strName6
Sheet7.Name = strName7
End If
End Sub