Tạo các trang tính trong tháng không có cuối tuần và ngày lễ bằng VBA

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