Create Sheets Based on Month Names

     

    How to Use This Template:

  • Update the Month Numbers from available Drop down cells i.e E4 & H4 Cells

    •  

  • If we Entered 1 in E4 cell and 5 in H4 Cell, you are creating sheets from January to May

    •  

  • If you entered UNIQUE VALUES in both cells: New workbook will be created for that month only

    •  

  • Enter the Year in F7 Cell

    •  

  • Update the STUDENT NAMES(ANY OTHER NAME) in First column, This is Dynamic(We can update N Number of Names)

    •  

     

    Private Sub CommandButton1_Click()
    'Data validation for Month Numbers
     Call MonthNumbers_For_DataValidation
    Min = Range("E4").Value: Max = Range("H4").Value
    If Min > Max Then
    MsgBox "Value in H4 cell value should be greater than E4"
    Exit Sub
    End If
    'Denotes about Number of worksheets in New workbook
     Application.SheetsInNewWorkbook = Max - Min + 1
    Dim wkb As Workbook
    Set wkb = Workbooks.Add
    Dim s As Integer
    s = 1
    ye = Range("F7").Value
    'Loop Defined based on Month Numbers assigned
     For i = Min To Max
    wkb.Sheets(s).Activate
    For d = 1 To Day(DateSerial(ye, i + 1, 0))
    'denotes about date
     wkb.Sheets(s).Cells(1, d + 1) = DateSerial(ye, i, d)
    'denotes about Weekday Name
     wkb.Sheets(s).Cells(2, d + 1) = WeekdayName(Weekday(wkb.Sheets(s).Cells(1, d + 1)))
    Next
    'copy Student Names
     For c = 1 To Range("A1").End(xlDown).Row
    wkb.Sheets(s).Range("A" & c).Value = ThisWorkbook.Sheets(2).Range("A" & c).Value
    Next
    'Cell decorations, change as per your requirement
     Call Cell_Decorations
    wkb.Sheets(s).UsedRange.Columns.AutoFit
    wkb.Sheets(s).Name = MonthName(i)
    s = s + 1
    Next
    Application.SheetsInNewWorkbook = 3
    End Sub

     

    Download The Workbook

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli