Thread: Renaming TABS via Macro to match days of the new month

This has to be in this form somewhere but I must not be wording it in the search correctly. So please point me in the right direction if I am in error.

I currently have a workbook master file that I have to copy at the start of each month to track sales for that month. As part of the change I have to rename the TABS
For example this month is 12.01.11, 12.02.11, 12.05.11 (note that the 3rd and 4th are weekends and have no data so I skip them)
Now January roles around and 01.02.12, 01.03.12 and so on.

I am very new to Macros but I am also confident that a Macro could do this but I cant seem to get off the launch pad here.

If it matters I do have a totals page which does a lot of tab cell referencing ie "=sum('12.01.11:12.30.11'!H44)" That formula obviously changes month to month.

Thanks in advance for any help.

A quick question - do you want the existing tabs to be renamed? Or should these tabs be created in a fresh blank workbook?

Do you have any other sheets other than the sheets with dates? If no, should all the sheets from 1 to end be renamed with the date? What if you have only 30 and the month has 31 days? Do we make a copy of an existing tab and rename it to 31?

You could try something like this.

First create a workbook that has two sheets named Begin and End with End right after Begin.
Then run this macro. It will create 31 worksheets between Begin and End.

Sub MakeDaySheets()
    Dim i As Long
    For i = 1 To 31
        ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets("Begin")
    Next i
End Sub

Then delete that macro (or never run it again).

Then this will clear the data from the sheet, rename them to the month specified and hide Saturday's and Sundays.
Currently the clear data section is a blunt instrument (clears all cells), but that could be altered to leave headers etc un-cleared.

Sub ReMonthWorkSheets()
    Dim currentSheet As Worksheet
    Dim uiDate As String
    Dim currentDate As Date, dateForSheet As Date
    Dim i As Long
    uiDate = Application.InputBox("Enter the date to rename sheets", Default:=Date, Type:=2)
    If uiDate = "False" Or Not (IsDate(uiDate)) Then Exit Sub
        currentDate = CDate(uiDate)
    
    Set currentSheet = ThisWorkbook.Worksheets("Begin")
    dateForSheet = DateSerial(Year(currentDate), Month(currentDate), 0)
    uiDate = "This will rename the sheets for " & Format(currentDate, "mmmm yyyy") & vbCr & "There is no Un-Do!"
    If MsgBox(uiDate, vbOKCancel + vbDefaultButton2) = vbCancel Then Exit Sub
    
    Do
        dateForSheet = dateForSheet + 1
        Set currentSheet = currentSheet.Next
        currentSheet.Name = Format(dateForSheet, "mm.dd.yy")
        
        Rem clear old data
        currentSheet.Cells.ClearContents
        
        Rem hide Saturday and Sunday
        If Month(dateForSheet) <> Month(currentDate) Or Format(dateForSheet, "ddd") = "Sat" Or Format(dateForSheet, "ddd") = "Sat" Then
            
            currentSheet.Visible = xlSheetHidden
        Else
            currentSheet.Visible = xlSheetVisible
        End If
    Loop Until currentSheet.Next.Name = "End"
    ThisWorkbook.Worksheets("Begin").Visible = xlSheetVeryHidden
    ThisWorkbook.Worksheets("End").Visible = xlSheetVeryHidden
End Sub

One use of the Begin and End sheets is that on your summary sheet, you can use the unchangin formula =SUM(Begin:End!H44), rather than the varying "=SUM('12.01.11:12.30.11'!H44)"