renaming excel worksheets

[User_5]

Hi Sisyphus,

Thanks for your constructive comments. I'm not a big fan of multi-line statments using "space + underscore"
so that's one reason I don't use Iif very much unless speed is critical, which is not the case here. The other
reason I hardly use it is because I tend to forget it exists! I've not used VBA in almost sevenyears and I'm
quite rusty.Thanks for reminding me.

As for leap years, you are 100% correct. I was basing my assumption on the fact that a Sunday falling on
Dec. 31st in a leap year would be a rare occurrence. But I found one in the year 2028.
So I added some lines to check any year to see if its a leap year and extend the DaysInYear to 366 if it is.
Thanks for pointing this out.

Before showing my amended code, I want to show anyone that wants to play around with it, a block of
code that will delete all the sheets that were added to the workbook so they can run the code again
without closing the workbook.
It's best if you start with a new workbook and not have any other workbooks open so you don't delete sheets
from the wrong workbook. You must delete all worksheets until only one worksheet exists, then rename it
something like "sss".

Code: Select all

Sub DeleteSheets()
    Dim Sht As Worksheet, i As Integer
    Application.DisplayAlerts = False
    For Each Sht In ThisWorkbook.Worksheets
        If Worksheets.Count = 1 Then Exit Sub
        Sht.Delete
    Next Sht
    Application.DisplayAlerts = True
End Sub


See my next post with the amended code.

[User_5]

With this code, you can set the day of the weekto any day. It also gives a slightly more (slick?) look
with the dates being shown as ordinal numbers (1st, 2nd , etc).

Code: Select all

Sub AddAndNameWeeklySheets()
    'Adds and names weekly worksheets for a given year and
    'day of week to the active workbook.
    Dim shtNum As Integer
    Dim newItem As Integer
    Dim userYear As Integer
    Dim userDay As Integer
    Dim newMonth As Integer
    Dim DaysInYear As Integer
    Dim ws As Worksheet
    Dim DVal As Date
    Dim collDays As Collection 'Collection of the userDay dates.
    Dim Months() As Variant   'Array that stores the months
    Dim ShowYear As Boolean
 
    '-Change the FOUR-DIGIT number to the desired year---------
       
                userYear = 2012
    '-----------------------------------------------------------

    '--Define the day of week you want to use to date the sheets---
    '--(1= Sun, 2= Mon, 3= Tue, 4= Wed, 5=Thu,6= Fri,7= Sat)-------
               
                userDay = 1
 
    '-------------------------------------------------------------
   
    '---Set to True or False to append the year to sheet name------
    'No need to ever change the next line
               
                ShowYear = False
 
    'Just remove the apostophe in in the next line to show the year
               
                'ShowYear = True
 
  '--------------------------------------------------------------
     If userDay < 1 Or userDay > 7 Then MsgBox "Invalid userDay Number", vbCritical

    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "x"
    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
 
    DVal = DateValue("Jan 1," & userYear) - 1
   
    'Check for leap years and assign the proper number of days.
 
    If userYear Mod 100 = 0 Then
        If userYear Mod 400 = 0 Then
            DaysInYear = 366
            MsgBox userYear & " is a leapyear"
    Else
            DaysInYear = 365
        End If
    Else
        If userYear Mod 4 = 0 Then
            DaysInYear = 366
            MsgBox userYear & " is a leapyear"
        Else
            DaysInYear = 365
        End If
    End If
   
     'Build a Collection of all assigned weekday dates for the given year
     Set collDays = New Collection
     For newItem = 1 To DaysInYear
         DVal = DVal + 1
         If Weekday(DVal) = userDay Then
             collDays.Add DVal
         End If
     Next newItem
    '-------------------------------------------------------------
 
    'This version shows dates as ordinal numbers(Jan 1st, Jun 3rd, Dec 13th, etc.)

  'Add Sheets and name them
  For shtNum = 1 To collDays.Count
    newMonth = Month(collDays(shtNum))
    If ShowYear = True Then
       Set ws = ThisWorkbook.Worksheets.Add(before:=Worksheets("x"))
       ws.Name = Months(newMonth - 1) & " " & ord(Day(collDays(shtNum))) & " " & userYear
    ElseIf ShowYear = False Then
       Set ws = ThisWorkbook.Worksheets.Add(before:=Worksheets("x"))
       ws.Name = Months(newMonth - 1) & " " & ord(Day(collDays(shtNum)))
    Else
       Exit Sub
    End If
  Next shtNum
  ThisWorkbook.Worksheets(1).Select

   'Clean-up
      Application.DisplayAlerts = False
      Worksheets("x").Delete
      Application.DisplayAlerts = True
      Set collDays = Nothing

End Sub


Private Function ord(Number)
  'Changes a number to an ordinal number.
      Select Case Right(Number, 1)
        Case Is = 1
          ord = Number & "st"
        Case Is = 2
          ord = Number & "nd"
        Case Is = 3
          ord = Number & "rd"
        Case Is = 0, 4 To 9
          ord = Number & "th"
      End Select
 'Special case must be made for numbers 11 through 13 only
      Select Case Number
        Case 11 To 13
            ord = Number & "th"
      End Select
    If Number = 0 And Len(Number) = 1 Then
        ord = 0
    End If
End Function


Please try it and let me know of any flaws. Thanks again for your comments Sisyphus and
for keeping me on my toes.

[Sisyphus]

Hello User_5,
I think you have covered all the bases now, and thanks for taking my challenge in the spirit in which it was given.
Members and guests browsing this subject now have two viable choices, demonstrating that Excel mostly has more than one way to do things. Here are the choices for easy reference. The difference is in the way days are picked.
1. Look for the first Sunday in the year, then pick each 7th day until the year's end.
2. Create a collection of all Sundays in a year, then loop through the collection.
Either system offers the capability of picking any year, any weekday, and sorting sheets left to right or right to left or formatting the tab names to your liking.
Why can come up with a third way?