Create day sheets of a month without weekends and holidays using VBA

In this article, we will create a macro to create a sheet for each weekday for the specified month of the specified year excluding all the dates specified in the holiday list.

Before running the macro, three inputs are required. We need to specify the month number in cell J10, year in cell J11 and specify the holiday date list in the range B16:B26.

After specifying the input values, click on the submit button to run the macro.

This macro will insert a new sheet for each weekday for the specified month excluding the dates specified in the holiday list.

Logic explanation

In this macro, we have used DateSerial function to find the last date of the specified month. We have used FOR Loop to loop from starting date of the month to last date of the month. We have used Find function to find whether date being used exists in the specified holiday list.

Weekday function is used along with If statement to check whether a date is weekday or weekend. If statement will insert a new sheet only if the date is a weekday and it doesn’t exists in the holiday list. As one can see in the above screenshot, sheet for 6^th December is not created, as 6^th December is present in the holiday list.

Please follow below for the code

Option Explicit

Sub MonthApply()

'Declaring variables
Dim DVariable As Date
Dim RngFind As Range
Dim MonthNo, YearNo As Integer
Dim StartDate, EndDate As Date

'Disabling the screen updates
Application.ScreenUpdating = False

With Worksheets("Main")
    
    'Getting month and year from cell J10 and J11 from "Main" sheet
    MonthNo = .Range("J10").Value
    YearNo = .Range("J11").Value
    
    'Deriving start and end date
    StartDate = DateSerial(YearNo, MonthNo, 1)
    EndDate = DateSerial(YearNo, MonthNo + 1, 0)
    
    'Looping through all the dates in the specified month
    For DVariable = StartDate To EndDate
    
        'Finding if date is marked as holiday
        Set RngFind = .Range("B16:B26").Find(DVariable)
                
        'Checking whether date is holiday, weekend or weekday
        If RngFind Is Nothing And Weekday(DVariable, 2) < 6 Then
        
            'Inserting new sheet after the last worksheet in the workbook
            Worksheets.Add after:=Worksheets(Worksheets.Count)
            
            'Renaming the active sheet
            ActiveSheet.Name = Format(DVariable, "dd.mm.yy")
        End If
        
    Next DVariable
    
    .Select
End With

Application.ScreenUpdating = True

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com