Dates Across Multiple Worksheets and Worksheets Title with Dates

[ohthehorror]

Hello,

I'm looking to create separate worksheets for 52 different work weeks starting July 1, 2013. Each worksheet will have Monday-Saturday columns for scheduling.
1. Is there a quick way to get each worksheet's name to be that of the Monday start date?
2. Is there a quick way to have the dates for each week after week one auto-fill?

I've done some searching, but come up empty on a solution that fills both criteria. These will save me a ton of time if possible. Much obliged for any suggestions provided.

[tigeravatar]

ohthehorror,

Welcome to the forum!
Give this a try:

Sub tgr()
    
    Dim ws As Worksheet
    Dim dtIndex As Date
    Dim i As Long
    
    dtIndex = CDate("July 1, 2013")
    
    For i = 1 To 52
        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
        With ws
            .Name = Format(dtIndex, "mmm dd, yyyy")
            With .Range("B1:G1")
                .Value = Array(dtIndex, dtIndex + 1, dtIndex + 2, dtIndex + 3, dtIndex + 4, dtIndex + 5)
                .NumberFormat = "dddd - mmm dd, yyyy"
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .ColumnWidth = 25
            End With
        End With
        dtIndex = dtIndex + 7
    Next i
    
    Set ws = Nothing
    
End Sub

[ohthehorror]

Thanks tiger. If nothing else this will save a lot of time.
I probably should have mentioned this in the original questions, but is there anyway to apply the layout and format from the master sheet to the created 52 sheets?
Again, thank you for any help.

[HaHoBe]

Hi, ohthehorror,

instead of adding a new sheet like

Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

try copying the Master-Sheet

Sheets("Master").Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet

Ciao,
Holger

[ohthehorror]

Holger,

That worked splendidly, thanks a billion to both of you.