excel code to create multiple worksheets

Question

Hello,

i am trying to create multiple worksheets for an entire year. Using the following code I found online, it will create one month at a time but not in order. i am not too familiar with this type of code. I am please with the part of code that labels each worksheet as Mmm dd, yyyy (Ddd).

Please review this code i found to see what changes need to be made so that it will create an entire fiscal year (Oct 1, 2022 to Sep 30, 2023) and put them in the proper order.

Sub DoDays()

Dim J As Integer 

Dim K As Integer 

Dim sDay As String 

Dim sTemp As String 

Dim iTarget As Integer 

Dim dBasis As Date 

iTarget = 13 

While (iTarget < 1) Or (iTarget > 12) 

    iTarget = Val(InputBox("Numeric month?")) 

    If iTarget = 0 Then Exit Sub 

Wend 

Application.ScreenUpdating = False 

sTemp = Str(iTarget) & "/1/" & Year(Now()) 

dBasis = CDate(sTemp) 

For J = 1 To 31 

    sDay = Format((dBasis + J - 1), "Mmm dd, yyyy (Ddd)") 

    If Month(dBasis + J - 1) = iTarget Then 

        If J <= Sheets.Count Then 

            If Left(Sheets(J).Name, 5) = "Sheet" Then 

                Sheets(J).Name = sDay 

            Else 

                Sheets.Add.Move after:=Sheets(Sheets.Count) 

                ActiveSheet.Name = sDay 

            End If 

        Else 

            Sheets.Add.Move after:=Sheets(Sheets.Count) 

            ActiveSheet.Name = sDay 

        End If 

    End If 

Next J 

For J = 1 To (Sheets.Count - 1) 

    For K = J + 1 To Sheets.Count 

        If Right(Sheets(J).Name, 10) > \_ 

          Right(Sheets(K).Name, 10) Then 

            Sheets(K).Move Before:=Sheets(J) 

        End If 

    Next K 

Next J 

Sheets(1).Activate 

Application.ScreenUpdating = True 

End Sub

Thank you,

RJW75

Answer 1

Hi,

'

open a new workbook

and paste in the below vba macro

'

Sub DoDays22()

'## 02 JUNE 2022 ##

'### NOTE!!! >> dates as [DAY/MONTH/YEAR] or [MONTH/DAY/YEAR] , dates depend on regional settings ###

Const sDay1 As String = "10/1/2022" '01 Oct 2022, start date

Const sDay2 As String = "9/30/2023" '30 Sep 2023, last date

'

Dim sh As Worksheet

Dim n1 As Long

Dim n2 As Long

Dim x As Long

'

n1 = CDate(sDay1)

n2 = CDate(sDay2)

'

If n1 >= n2 Then

MsgBox "wrong dates"

Exit Sub

End If

'

Application.ScreenUpdating = False

Application.DisplayAlerts = False

'

If Worksheets.Count > 1 Then

For x = Worksheets.Count To 2 Step -1

Sheets(x).Delete

Next x

Sheets(1).Name = "tmp"

End If

'

For x = n1 To n2

Set sh = Sheets.Add(after:=Sheets(Sheets.Count))

sh.Name = Format(x, "Mmm dd, yyyy (Ddd)")

Next x

Sheets(2).Select

Sheets(1).Delete

'

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Answer 2

Thank you, that is exactly what i needed.

Answer 3

Re: Create Months workbook

You can click and drag a worksheet tab to any sheet position desired.

If you still want to go ahead using code try this alternative of mine...

The free "Create DayWeekMonth_Workbooks."

The Sheets in the month workbook look like...

Plus the first sheet is a year calendar.

Download from OneDrive... https://1drv.ms/f/s!Au8Lyt79SOuhZw2MCH7_7MuLj04

'---

Nothing Left to Lose