Creating a Calendar for a Month with tasks

[anuradhagrewal]

Hi
I have prepared a calendar file
What I am looking is at that if I enter anything in the worksheet Main under TASK (Column D) then the value of the task should appear in Calender.
The worksheet "Sample" is how I want the layout to be
So lets say in worksheet Main in C2 having date 01-04-2024 I enter a task in the corresponding column D2 then the VB code should pick this up and using the value of B2 in worksheet main which the day of the week.
It should make a new calendar worksheet named Calendar for the Month of (taking the value of the first day of the month as defined in worksheet Main C2 so in this case Calender for the Month of April)
As the month changes in column C of worksheet main a new worksheet should be made only for that month using the sample worksheet again and the whole process be repeated for the next month and so on.
Then using the sample layout enter the value of worksheet Main D2 in the corresponding cells next the value of the day of the week and below that enter the date in dd-mmm-yyyy format.
I tried this code but its not working

Sub UpdateCalendar()
    Dim wsMain As Worksheet
    Dim taskDate As Date
    Dim task As String
    Dim calendarMonth As String
    Dim calendarYear As String
    Dim calendarSheet As Worksheet
    Dim lastRow As Long

    ' Set reference to the Main worksheet
    Set wsMain = ThisWorkbook.Sheets("Main")

    ' Retrieve task details from the Main worksheet
    taskDate = wsMain.Range("C2").Value
    task = wsMain.Range("D2").Value
    
    ' Get the year and month from the task date
    calendarYear = Year(taskDate)
    calendarMonth = Format(taskDate, "mmmm")

    ' Check if Calendar sheet for the month exists, if not create it
    On Error Resume Next
    Set calendarSheet = ThisWorkbook.Sheets("Calendar - " & calendarMonth & " " & calendarYear)
    On Error GoTo 0
    
    If calendarSheet Is Nothing Then
        ' Create a new worksheet for the calendar for the specific month
        Set calendarSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        calendarSheet.Name = "Calendar - " & calendarMonth & " " & calendarYear
        ' Apply the Sample layout to the new calendar sheet
        
        ' Assuming the Sample layout is in a sheet called "Sample"
        ThisWorkbook.Sheets("Sample").UsedRange.Copy calendarSheet.Range("A1")
    End If
    
    ' Find the first empty row in the calendar sheet and enter the task details
    lastRow = calendarSheet.Cells(calendarSheet.Rows.Count, 1).End(xlUp).Row + 1
    calendarSheet.Cells(lastRow, 1).Value = task
    calendarSheet.Cells(lastRow, 2).Value = Format(taskDate, "dddd")
    calendarSheet.Cells(lastRow, 3).Value = Format(taskDate, "dd-mmm-yyyy")
End Sub

Thanks

Anuradha