How to get working days of a month in serial order

[ThrottleWorks]

Hi,

I am trying to get working days from a month in serial order.
I am using below mentioned code to get the result.
Thought the code is giving me correct results, can anyone please help me with better solution.

Please see my code below.

Option Explicit

Sub Get_Working_Days()
    Dim Macrobook As Workbook
    Dim MacroSht As Worksheet
    Dim MapSht As Worksheet
   
    Set Macrobook = ThisWorkbook
    Set MacroSht = Macrobook.Worksheets("Macro")
    Set MapSht = Macrobook.Worksheets("Mapping")
   
    MapSht.Select
    MapSht.Range("A1") = "Date"
    MapSht.Range("B1") = "Week Num"
    MapSht.Range("C1") = "New Date Range"
   
    MapSht.Range("A2").FormulaR1C1 = "=DATE(R[32]C,R[31]C,1)"
   
    MapSht.Range("A33").FormulaR1C1 = "=MONTH(TODAY())"
    MapSht.Range("A34").FormulaR1C1 = "=YEAR(TODAY())"
    MapSht.Range("A35").Value = 1
    MapSht.Range("A36").Value = 7

    MapSht.Range("A3:A32").FormulaR1C1 = "=R[-1]C+1"
    MapSht.Range("B2:B32").FormulaR1C1 = "=IF(MONTH(RC[-1]=R33C1),(IFERROR(VLOOKUP((WEEKDAY(RC[-1])),R35C1:R36C1,1,0),0)),7)"
   
    Dim TempLr As Long
    Dim TempRng As Range
    Dim TRng As Range
   
    MapSht.Range("C2:C65000").ClearContents
    TempLr = MapSht.Cells(MapSht.Rows.Count, 3).End(xlUp).Row + 1
    Set TempRng = MapSht.Range(MapSht.Cells(2, 1), MapSht.Cells(32, 1))
   
    For Each TRng In TempRng
        If MapSht.Cells(TRng.Row, 2) = 0 Then
            TempLr = MapSht.Cells(MapSht.Rows.Count, 3).End(xlUp).Row + 1
            MapSht.Cells(TempLr, 3) = MapSht.Cells(TRng.Row, 1)
        End If
    Next TRng
End Sub

 

[NARAYANK991]

Hi ,

Explore the Range.DataSeries method.

Narayan

 

[ThrottleWorks]

Hi @NARAYANK991 sir, thanks for the help. Have a nice day ahead.