Name 52 worksheets as week beginning based on cell reference

[Sam Longstaff]

Hi,

I have a VBA code that creates and names 52 new worksheets as "wk beg" plus the date.

Sub NameSheetsByMonday()
Dim i As Integer
    For i = 0 To 51
        Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "wk beg " & Format(#1/6/2020# + (i * 7), "dd-mmm-yyyy")
    Next
End Sub

Presently I have to manually enter the date into the VBA before running it to create the new workbook. I would like the VBA to reference a cell in my template worksheet so that this can be changed and then the VBA run. This would allow for someone to create the workbook without having to enter the developer.

What I should point out is that the date in the reference cell will have UK date formatting, not US date format as shown in the VBA above. Would I need an amendment for that too?

Many thanks in advance.

 

[Logit]

See if this works :

Option Explicit

Sub NameSheetsByMonday()
Dim i As Integer
    For i = 0 To 51
        Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "wk beg " & Format(Range("A1").Value + (i * 7), "dd-mmm-yyyy")
    Next
End Sub

 

[Sam Longstaff]

Sorry I should have said that my range cell would be in another sheet (named "Calendar"). How would I get it to look for this cell please?

 

[Logit]

Option Explicit

Sub NameSheetsByMonday()
Dim i As Integer
    For i = 0 To 51
        Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = "wk beg " & Format(Sheets("Calendar").Range("A1").Value + (i * 7), "dd-mmm-yyyy")
    Next
End Sub

 

[Sam Longstaff]

That works a treat, thank you very much!

 

[Logit]

You are welcome.