• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Name 52 worksheets as week beginning based on cell reference

Hi,

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

Code:
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.
 
Last edited by a moderator:
See if this works :

Code:
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
 
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?
 
Code:
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
 
Back
Top