Forum Discussion

kvhpkh's avatar
kvhpkh
Copper Contributor
Nov 04, 2022

Creating and Dating multiple sheets 2 weeks apart

How can I create and name 26 sheets at the same time? 

 

I need to date them two weeks apart, starting with the date of the second Friday in January - January 13, 2023.   The sheets following need to be dated 2 weeks apart, i.e.  January 27, Feb. 10, Feb. 24, March 10, etc.   

 

The date format doesn't matter but, my preference would be:   Jan 13, Jan 27, Feb 24...........and so on.  The file will be named with the year 2023.

excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 04, 2022

    kvhpkh 

    You can run this macro:

    Sub CreateSheets()
    Dim y As Long
    Dim w As Long
    Dim d As Date
    Dim i As Long
    y = InputBox(Prompt:="Enter the year", Default:=Year(Date) + 1)
    w = Weekday(DateSerial(y, 1, 1), vbSaturday)
    d = DateSerial(y, 1, 1) + 14 - w
    Application.ScreenUpdating = False
    For i = 0 To 25
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Format(d + 14 * i, "mmm d")
    Next i
    Application.ScreenUpdating = True
End Sub
    • kvhpkh's avatar
      kvhpkh
      Copper Contributor
      Dec 30, 2022

      I have never run a macro in Excel before. I googled for instructions but, cannot find out how to do this specific macro. Would you mind giving me step by step instructions?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 30, 2022

        kvhpkh 

        Press Alt+F11 to activate the Visual Basic Editor.

        Select Insert > Module.

        Copy the code from my previous reply, and paste it into the code module.

        Click anywhere in the code, then press F5 to run it.

        Switch back to Excel and inspect the result.

        You probably won't need to run the macro again. When you close and save the workbook, Excel will display a warning. Click Yes to save the workbook anyway.

Resources