Formulas to Enter Events in Calendar Sheets

Hello,

Is it possible to have formulas to put the descriptions from the Events! sheet to the matching days in the calendars sheets? I put just a few examples of course there will be many more for the year.

Thank you for any help,
XJ

Why not just use the macro for adding holidays & change the sheet name in the code to Events

I tried that but it replaces the Holiday if there is one there.

Maybe the code can be altered to incorporate both sheets (Holidays and Events)?

I tried an example see January 21.

Thank you for the reply but will that work?

As long as the Event sheet & Holiday sheet are setup the same, then it should do.

How about

Sub ClearCells()
    Dim ws As Worksheet
    Dim i As Long
    For Each ws In Worksheets(Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"))
        With ws
            For i = 4 To 34 Step 6
                .Range(.Cells(i, 1), .Cells(i + 4, 14)).ClearContents
            Next i
        End With
    Next ws
End Sub


Sub AddEventsToSheets()
   Call ClearCells
   Dim ws As Worksheet, sh As Worksheet
   Dim Rng As Range, c As Range, fNd As Range
   Dim NrNg As Range, g As Range, m As String
   For Each ws In Sheets(Array("Holidays", "Events"))
      With ws
         Set Rng = .Range("A3:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
         For Each c In Rng.Cells
            m = Format(c, "MMMM")
            Set NrNg = Sheets(m).Range("A3:N33").SpecialCells(xlCellTypeFormulas, 23)
            For Each g In NrNg
               If g.Value = c.Value Then
                  g.Resize(6).SpecialCells(xlBlanks)(1).Value = c.Offset(, 1)
               End If
            Next g
         Next c
         Set Rng = .Range("H3:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
         For Each c In Rng.Cells
            If Month(c) = 1 Then
               m = "december"
               Set NrNg = Sheets(m).Range("A3:N33").SpecialCells(xlCellTypeFormulas, 23)
               For Each g In NrNg
                  If g.Value = c.Value Then
                     g.Resize(6).SpecialCells(xlBlanks)(1).Value = c.Offset(, 1)
                  End If
               Next g
            End If
         Next c
      End With
   Next ws
End Sub

Note the change in the Clearcells code

That works great thank you very much. I think this should cover the Holiday and up to 4 additional events for the same day.

Just curious if I face a very busy day and need more what are my options?

Cancel an appointment

Either concatenate some of the events together in one cell, or increase the number of cells available.

Thank you Fluff I will make some notes for those ideas.