Tip Printed from ExcelTip.com
Create day sheets of a month without weekends and holidays using VBA


Question: How can I create a Worksheet in which the weekends and holidays are absent, for every day of the month in the current Workbbook ? The day sheets should be named with the date.

Answer: Enter the following Code with Excel5/7 in an ModuleSheet, with Excel8 in a general Module, assign it to a Button and run it.

Place the code below into the standard module




Sub CallingMonths()

   Application.ScreenUpdating = False

   Call MonthApply(2000, 12)

   Application.ScreenUpdating = True

End Sub



Sub MonthApply(intYear As Integer, intMonth As Integer)

   Dim datDay As Date

   Dim dblDay As Double

   Dim rngFind As Range

   For dblDay = DateSerial(intYear, intMonth, 1) To _

      DateSerial(intYear, intMonth + 1, 0)

      Set rngFind = Worksheets("Holidays").Columns(1). _

         Find(DateValue(Format(DateSerial(intYear, intMonth, 1), "dd.mm.yy")), LookIn:=xlFormulas)

      If rngFind Is Nothing And WorksheetFunction.WeekDay(dblDay) < 6 Then

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

         ActiveSheet.Name = Format(dblDay, "dd.mm.yy")

      End If

   Next dblDay

   Worksheets(1).Select

End Sub