rename tabs in excel

Microsoft Office excel 2007 home & stude...
July 26, 2010 at 18:03:09
Specs: Windows Vista
A post by Derbydad03 gives a VBA code to add tabs renamed with a given date. Is it possible to use that code but using a date starting from sheet 1 cell A1. Also, is it possible to add on the tab name the weekday name as well?

here is the VAB code:

Sub SheetsByDate()
Dim dateRow, myDate, myDay, newSht
'Initialize dateRow Variable
dateRow = 1
'Loop to put Dates and Days in Columns A & B
'(40179 = 1/1/2010)
For myDate = 40179 To 40179 + 365
myDay = WeekdayName(Weekday(myDate))
'Skip Weekends
If myDay = "Sunday" Or myDay = "Saturday" _
Then GoTo WeekEndDay
'Increment dateRow
dateRow = dateRow + 1
'Populate cells (the weekday names aren't used
'other than for a "visual" check
Sheets(1).Cells(dateRow, 1) = myDate
Sheets(1).Cells(dateRow, 2) = myDay
WeekEndDay:
Next
'Loop to Add sheets and Rename them
For newSht = 2 To dateRow
'Add sheets
Sheets.Add after:=Sheets(Sheets.Count)
'Rename them based on Dates in Column A
'Format = mm-dd-yyyy
ActiveSheet.Name = _
Month(Sheets(1).Cells(newSht, 1)) & _
"-" & Day(Sheets(1).Cells(newSht, 1)) & _
"-" & Year(Sheets(1).Cells(newSht, 1))
Next
End Sub

thank you.


See More: rename tabs in excel

Report •

#1
July 27, 2010 at 05:21:50
This line will name the ActiveSheet based on the date in A1 of the ActiveSheet. The resulting name will look like this:

Friday 4-3-2009

Sub Name_By_A1_date()
ActiveSheet.Name = WeekdayName(Weekday(Cells(1, 1))) & _
" " & Month(Cells(1, 1)) & "-" & Day(Cells(1, 1)) & _
"-" & Year(Cells(1, 1))
End Sub


Report •

#2
August 2, 2010 at 15:38:50
That's great thanks. Is it possible to add more than one tab omitting saturday and sunday like the original code below?

Sub SheetsByDate()
Dim dateRow, myDate, myDay, newSht
'Initialize dateRow Variable
dateRow = 1
'Loop to put Dates and Days in Columns A & B
'(40179 = 1/1/2010)
For myDate = 40179 To 40179 + 365
myDay = WeekdayName(Weekday(myDate))
'Skip Weekends
If myDay = "Sunday" Or myDay = "Saturday" _
Then GoTo WeekEndDay
'Increment dateRow
dateRow = dateRow + 1
'Populate cells (the weekday names aren't used
'other than for a "visual" check
Sheets(1).Cells(dateRow, 1) = myDate
Sheets(1).Cells(dateRow, 2) = myDay
WeekEndDay:
Next
'Loop to Add sheets and Rename them
For newSht = 2 To dateRow
'Add sheets
Sheets.Add after:=Sheets(Sheets.Count)
'Rename them based on Dates in Column A
'Format = mm-dd-yyyy
ActiveSheet.Name = _
Month(Sheets(1).Cells(newSht, 1)) & _
"-" & Day(Sheets(1).Cells(newSht, 1)) & _
"-" & Year(Sheets(1).Cells(newSht, 1))
Next
End Sub

Thanks much,


Report •

#3
August 2, 2010 at 18:38:19
Sub SheetsByDate()
Dim dateRow, myDate, myDay, newSht
'Initialize dateRow Variable
 dateRow = 1
'Loop to put Dates and Days in Columns A & B
'(40179 = 1/1/2010)
  For myDate = 40179 To 40179 + 365
   myDay = WeekdayName(Weekday(myDate))
'Skip Weekends
    If myDay = "Sunday" Or myDay = "Saturday" _
     Then GoTo WeekEndDay
'Increment dateRow
    dateRow = dateRow + 1
'Populate cells (the weekday names aren't used
'other than for a "visual" check
    Sheets(1).Cells(dateRow, 1) = myDate
    Sheets(1).Cells(dateRow, 2) = myDay
WeekEndDay:
  Next
'Loop to Add sheets and Rename them
  For newSht = 2 To dateRow
'Add sheets
   Sheets.Add after:=Sheets(Sheets.Count)
'Rename them based on Dates in Column A
'Format = mm-dd-yyyy
    ActiveSheet.Name = WeekdayName(Weekday(Cells(newSht, 1))) & _
    " " & Month(Cells(newSht, 1)) & "-" & Day(Cells(newSht, 1)) & _
    "-" & Year(Cells(newSht, 1))
  Next
End Sub



Report •
Related Solutions


Ask Question