Sheets for Days

When you are starting a new workbook, it is very common to name each worksheet after a different day of the month. If you do this quite a bit, you know it can be tiresome to rename each worksheet, individually, to exactly what you need.

The following macro was developed to help in these situations. It checks the names of the worksheets in your workbook, renaming them to the days of the month if they begin with the letters "Sheet". If there are not enough sheets in the workbook, it adds sheets, as necessary, for each day of the month.

Sub DoDays()
    Dim J As Integer
    Dim K As Integer
    Dim sDay As String
    Dim sTemp As String
    Dim iTarget As Integer
    Dim dBasis As Date

    iTarget = 13
    While (iTarget < 1) Or (iTarget > 12)
        iTarget = Val(InputBox("Numeric month?"))
        If iTarget = 0 Then Exit Sub
    Wend

    Application.ScreenUpdating = False
    sTemp = Str(iTarget) & "/1/" & Year(Now())
    dBasis = CDate(sTemp)
    
    For J = 1 To 31
        sDay = Format((dBasis + J - 1), "dddd mm-dd-yyyy")
        If Month(dBasis + J - 1) = iTarget Then

            If J <= Sheets.Count Then
                If Left(Sheets(J).Name, 5) = "Sheet" Then
                    Sheets(J).Name = sDay
                Else
                    Sheets.Add.Move after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = sDay
                End If
            Else
                Sheets.Add.Move after:=Sheets(Sheets.Count)
                ActiveSheet.Name = sDay
            End If
        End If
    Next J

    For J = 1 To (Sheets.Count - 1)
        For K = J + 1 To Sheets.Count
            If Right(Sheets(J).Name, 10) > _
              Right(Sheets(K).Name, 10) Then
                Sheets(K).Move Before:=Sheets(J)
            End If
        Next K
    Next J

    Sheets(1).Activate
    Application.ScreenUpdating = True
End Sub

Note that the macro assumes that the month for which you want worksheets is in the current year. If that is not the case, you'll need to make one small change. Note the following line near the beginning of the macro:

    sTemp = Str(iTarget) & "/1/" & Year(Now())

Let's say that you actually want the month to be in 2015 for some reason. Just change the line to the following:

    sTemp = Str(iTarget) & "/1/2015"

The macro sets each tab name equal to the day of the week followed by the actual date, as in "Wednesday 03-28-2020." If you want to change the way that the tabs are named for each day, just change how the sDay variable is constructed in the macro.

The last step in the macro is that it places the worksheets in proper order, based on the days of the month. The result is that if you have any other worksheets left in the workbook (in other words, you had some that did not begin with the letters "Sheet," then those worksheets end up at the end of the workbook, after the sheets for each day.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11523) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Sheets for Days.