How can I make a simple calendar using VBA (image is given below for reference) ?

[abhi221996]

I want to make a simple calendar like this using VBA and also I want exclude weekends from the calendar .Any suggestions?

 

[earthworm]

Try this

Sub Inputdate()

Dim X As Integer

For X = 1 To 365
     
    Cells(X, 1) = X & "/1/2021"
    Next X
    Cells(1, 1).Select
    Selection.AutoFill Destination:=Range("A1:A365")
    Application.CutCopyMode = False
    Range("B1").FormulaR1C1 = "=RC[-1]"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B365")
    Range("B1:B365").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("B:B").Select
    Selection.NumberFormat = "dddd"
    With Selection
        .HorizontalAlignment = xlLeft
        Application.CutCopyMode = False
        Range("A1").Select
    End With
    
End Sub

 

[abhi221996]

Thank you. It worked but can't it be done by for loop.

Sub test()
Dim datr()
startrow = 3
endrow = 30
startdate = Date
ReDim datr(1 To endrow - startrow + 1, 1 To 2)
For i = 1 To (endrow - startrow) + 1 ' start row and end row

    datr(i, 1) = StartDate - 1 + i
    
    
Next i

End Sub

I tried this but I don't know for how to display days and exclude weekends

Try this

Sub Inputdate()

Dim X As Integer

For X = 1 To 365
    
    Cells(X, 1) = X & "/1/2021"
    Next X
    Cells(1, 1).Select
    Selection.AutoFill Destination:=Range("A1:A365")
    Application.CutCopyMode = False
    Range("B1").FormulaR1C1 = "=RC[-1]"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B365")
    Range("B1:B365").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columns("B:B").Select
    Selection.NumberFormat = "dddd"
    With Selection
        .HorizontalAlignment = xlLeft
        Application.CutCopyMode = False
        Range("A1").Select
    End With
   
End Sub

 

[Saurabhj]

Hi Abhi,

Check below:

Sub test()
Dim datr() As Long, startrow As Integer, endrow As Integer
Dim startDate As Date, i As Integer, rowno As Integer
startrow = 3
endrow = 30
startDate = Date
ReDim datr(1 To endrow - startrow + 1, 1 To 2)
rowno = 1
For i = 1 To (endrow - startrow) + 1 ' start row and end row
    datr(i, 1) = startDate - 1 + i
    If CInt(Weekday(datr(i, 1), vbMonday)) <= 5 Then
        Sheets("Sheet3").Range("A" & rowno) = Format(datr(i, 1), "MM/DD/YYYY")
        Sheets("Sheet3").Range("B" & rowno) = WeekdayName(Weekday(datr(i, 1), vbMonday))
        rowno = rowno + 1
    End If
Next i

 

[Fluff]

Duplicate to: how to get the following output(reference image below) using VBA for getting list of dates from a particular date to particular date?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.