Forum Discussion

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 ...

excel

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

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.

kvhpkh

Copper Contributor

Feb 08, 2024

I am trying to set up this year again. But, I can't recall how to get the template to copy on all of the sheets that this macro creates. Can you please help me figure that out? All of the sheets are blank and the template is filled out with text.

HansVogelaar

MVP

Feb 08, 2024

kvhpkh

Let's say you have a sheet named Template.

Change the macro as follows:

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("Template").Copy(After:=Worksheets(Worksheets.Count))
        Worksheets(Worksheets.Count).Name = Format(d + 14 * i, "mmm d")
    Next i
    Application.ScreenUpdating = True
End Sub

kvhpkh
Copper Contributor
Dec 30, 2022
Thanks so much! You have saved me a ton of work!