Creating A Separate Sheet For Each Month With Macro

Excel VBA Create The Separate Sheet For Each Month

           When "Create Month Sheets" button in worksheet is clicked, new sheets (12 sheets) are created for each month and added in workbook.
Namely months of the current year are ordered as sheets.

📅 Days of each month are listed in Column A as "dd.mm.yyyy". Today's date is selected from between dates.

When the "Delete Sheets" button is pressed, the created 12 sheets are deleted.

vba create sheet for each month

The VBA codes we use to create the months:
Sub CreateMonths()
   Dim lDay As Long
   Dim iWks As Integer, iDay As Integer
   On Error Resume Next
   For iWks = 1 To 12
      Worksheets.Add after:=Worksheets(Worksheets.Count)
      ActiveSheet.Name = Format(DateSerial(1, iWks, 1), "mmmm")
       ActiveWorkbook.Worksheets.Application.Columns("A:A").ColumnWidth = 11
   'ActiveWorkbook.Worksheets.Application.Columns("A:A").EntireColumn.AutoFit
      For lDay = DateSerial(Year(Date), iWks, 1) To DateSerial(Year(Date), iWks + 1, 0)
         iDay = iDay + 1
         Cells(iDay, 1).Value = DateSerial(Year(Date), iWks, iDay)
      Next lDay
      iDay = 0
   Next iWks
  GotoToDay
  End Sub

Sub GotoToDay()
   Dim iRow As Integer
   Worksheets(Month(Date) + 1).Select
   iRow = WorksheetFunction.Match(CDbl(Date), Columns(1), 0)
   Cells(iRow, 1).Select
End Sub

In the above codes, the GotoToDay procedure is run to select today's date.

creating month sheet macro

No comments:

Post a Comment