renaming existing worksheets (for each day in month)

[finchfinch]

Hi,

I have a monthly workbook, which is currently being managed manually. Massive pain as you can imagine. It has the following worksheets:
-Graph
-Data
-01-05-14....etc There is a worksheet for each day in the month.

How would I go about creating a macro to rename the existing worksheets for say June, July etc. I want to rename the existing worksheets because there are formulas linked to the each day on the data tab. Any help advice would be much appreciated.

I have the following code which creates a workbook for each month and creates the relevant worksheets, which is great. But I really need the ability to rename the existing worksheet names as there is a graph and data tab mentioned above:

Sub Add_Sheets_Months()
    Dim i As Integer
    For i = 1 To 12
    Call AddMonthSheets(i, 2014)
    Next i
End Sub

Public Sub AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer)
    Dim wks  As Worksheet
    Dim dte  As Date
    Dim lCounter As Long
    Dim wkbk   As Workbook
    Set wkbk = ThisWorkbook  'workbook with code and template sheet
    Set wks = wkbk.Sheets("Sheet1")    'Sheet to be copied assuming template for others
    Application.ScreenUpdating = False
    Workbooks.Add
    For lCounter = 1 To Day(DateSerial(Year(Now), Mnth + 1, 1) - 1)
        dte = DateSerial(Yr, Mnth, lCounter)
        If Month(dte) = Mnth And (Weekday(dte) <> 1 And _
                                  Weekday(dte) <> 7) Then
            wks.Copy after:=ActiveSheet
            ActiveSheet.Name = Format(DateSerial(Yr, Mnth, lCounter), "ddd Mmm dd ")
        End If
    Next lCounter
    Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveAs Filename:="C:\Temp" _
                          & "\" & Format(dte, "mmmm") & ".xlsx"
        .Close
    End With
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Thanks,

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

How do you currently handle renaming of the sheets that have less than 31 days? In your manual process what do you do with worksheets for Jan 29,30,31 when you update the sheet names for Feb?

 

[finchfinch]

Hi Jerry,

Currently we have spreadsheets for every month with the days already created. Which were setup manually last year. So we just copy the previous years template that was already created.

 

[Jerry Sullivan]

One approach would be to have a single template file for a single 31 day month based on a start date of 1/1/14.

The user would save a copy of the template file and change the value of a cell with a defined name such as "StartOfMonth" to be the first date of the desired month eg: "01-06-14" for June.

VBA could be used to rename the each worksheet based on the StartOfMonth value, then delete any unneeded worksheets for months that are less than 31 days.

Formulas in your workbook could also reference this StartOfMonth cell to allow dynamic titles and labels.

You would need to design your chart source data and charts so that they allow deleting of the unneeded worksheets without generating errors (your existing template might already be so designed).

Would that approach work for you?