How to Create day sheets of a month without weekends and holidays using VBA?
Tags: tocreatedaysheetsofa, vbamacros
Published: 2021-10-05 11:13:53

---
title: How to Create day sheets of a month without weekends and holidays using VBA?
published: true
date: 2021-10-05 07:04:01 UTC
tags: ToCreateDaySheetsOfA,VBAMacros
canonical_url: https://geekexcel.com/how-to-create-day-sheets-of-a-month-without-weekends-and-holidays-using-vba/
---

In this tutorial, we will guide you on how to  **create day sheets of a month without weekends and holidays** using VBA ** ** in Excel. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Example

- Firstly, you need to  **create a sample data**  with the **month number in cell J10, year in cell J11** and specify the **holiday date list** in the **range B16:B26**.

![](https://geekexcel.com/wp-content/uploads/2021/10/Sample-data-11.png)<figcaption>Sample data</figcaption>

- In the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```

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

```

- After that, you need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/10/Save-the-Code-9.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/10/Run-the-Code-9.png)<figcaption>Run the Code</figcaption>

- Finally, this macro will **insert a new sheet** for each weekday for the specified month, excluding the dates specified in the holiday list as given below.

![](https://geekexcel.com/wp-content/uploads/2021/10/Output-8.png)<figcaption>Output</figcaption>

## Verdict

We hope that this tutorial gives you guidelines on how to  **create day sheets of a month without weekends and holidays** using VBA in Excel. Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Read Also:**

- **[Excel Formulas Count Holidays Between Two Dates ~ Simple Formulas!!](https://geekexcel.com/excel-formulas-count-holidays-between-two-dates-simple-formulas/)**
- **[Excel Formulas to List Holidays between Two Dates ~ Easy Tips!!](https://geekexcel.com/excel-formulas-to-list-holidays-between-two-dates/)**
- **[Formulas to Highlight the Dates that are Weekends!!](https://geekexcel.com/excel-formulas-to-highlight-the-dates-that-are-weekends/)**
- **[Excel Formulas to Create a Series of Dates by Weekends ~ Quick Tips!!](https://geekexcel.com/excel-formulas-to-create-a-series-of-dates-by-weekends/)**
- **[Excel Formulas to Create Gantt Chart with Weekends ~ Easy Tutorial!!](https://geekexcel.com/excel-formulas-to-create-gantt-chart-with-weekends/)**