Import data from sheet into calendar

[squirrellydw]

I would like to be able to import some of the info in the screenshot into the calendar below. The calendar is found in excel, and can be found by typing in Calendar, it's called "Any Year one-month calendar1"

I would like to be able to import the last 3 columns into the correct date on the calendar but I wanna be able to pick what gets imported. For example only Seats 100 or more.

So April 1st, would list
DFW-190-0519
ATL-110-0605
CLT-128-0616 etc.

Is there an easy way I can do this? You guys have helped me in the past and I hope you can do it one more time. I can copy the data onto another tab of the calendar if that make it easier. I'm just looking for an automated way to do this.

Thanks

 

[Sam_D_Ben]

Something like this,

Please note, Your calendar range seems different from my calendar

Assuming your data is in Sheet named "Data", Calender is in Sheet named "Calender"

Book2
	A	B	C	D	E
1	Date	Mkt AI	Dest	Seats	Dep Time
2	Apr 1, 2025	AA	CLT	76	500
3	Apr 1, 2025	AA	DFW	190	519
4	Apr 1, 2025	AA	BOS	50	600
5	Apr 1, 2025	DL	ATL	110	605
6	Apr 1, 2025	UA	IAD	50	605
7	Apr 1, 2025	AA	CLT	128	616
8	Apr 1, 2025	UA	ORD	166	630
9	Apr 1, 2025	AA	PHL	50	642
10	Apr 1, 2025	AA	ORD	50	741
11	Apr 1, 2025	AA	CLT	50	830
12	Apr 1, 2025	UA	IAD	50	1015
13	Apr 1, 2025	G4	PGD	180	1036
14	Apr 1, 2025	UA	ORD	50	1052
15	Apr 1, 2025	AA	ORD	50	1058
16	Apr 1, 2025	G4	SRQ	180	1154
17	Apr 1, 2025	AA	CLT	50	1157
18	Apr 1, 2025	AA	PHL	50	1200
19	Apr 1, 2025	DL	ATL	110	1300
20	Apr 1, 2025	AA	CLT	76	1334
21	Apr 1, 2025	UA	IAD	50	1430
22	Apr 1, 2025	F9	MCO	186	1512
23	Apr 1, 2025	AA	CLT	50	1537
Data

I enabled the "Show Comment" option since the list is long exceeding the calender date box. If you want, you can modify to "Autofit" option.

Please modify the code according to your data structure

Sub ImportAndUpdateCalendar()
    Dim wsData As Worksheet, wsCalendar As Worksheet
    Dim lastRow As Long, i As Long
    Dim dateCell As Range
    Dim seatCount As Integer
    Dim eventCode As String, eventTime As String
    Dim eventDate As Date
    Dim eventDetails As String
    Dim calendarRange As Range
    Dim tooltipText As String
    
    Set wsData = ThisWorkbook.Sheets("Data") ' Your event data sheet
    Set wsCalendar = ThisWorkbook.Sheets("Calendar") ' Your calendar sheet
    
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    
    Set calendarRange = wsCalendar.Range("A7:M36") ' Modify
        
    For i = 2 To lastRow
        eventDate = wsData.Cells(i, 1).Value
        eventCode = wsData.Cells(i, 3).Value
        seatCount = wsData.Cells(i, 4).Value
        eventTime = wsData.Cells(i, 5).Value
        
        eventDetails = eventCode & " - " & seatCount & " seats - " & eventTime
        
        If seatCount >= 100 Then
            For Each dateCell In calendarRange
                If IsDate(dateCell.Value) And dateCell.Value = eventDate Then
                    If Not dateCell.Comment Is Nothing Then
                        tooltipText = dateCell.Comment.Text & vbNewLine & eventDetails
                        dateCell.Comment.Text Text:=tooltipText
                    Else
                        
                        dateCell.AddComment Text:=eventDetails
                        dateCell.Comment.Visible = False
                    End If
                    Exit For
                End If
            Next dateCell
        End If
    Next i
        
End Sub

 

[squirrellydw]

That’s close but the show comment part won’t work because I have to print this out. How do I enable the auto fit option?

 

[HighAndWilder]

I would like to be able to import some of the info in the screenshot into the calendar below. The calendar is found in excel, and can be found by typing in Calendar, it's called "Any Year one-month calendar1"

I would like to be able to import the last 3 columns into the correct date on the calendar but I wanna be able to pick what gets imported. For example only Seats 100 or more.

So April 1st, would list
DFW-190-0519
ATL-110-0605
CLT-128-0616 etc.

Is there an easy way I can do this? You guys have helped me in the past and I hope you can do it one more time. I can copy the data onto another tab of the calendar if that make it easier. I'm just looking for an automated way to do this.

Thanks

View attachment 123218View attachment 123219

In the Calendar that I have, the space below each day number is just one cell. See image attached.

Is this the case with yours?

If one date cell has numerous entries do you want just that row to autofit or do you want the each row on the calendar to resize to match?

What is the expected maximum number of flights per day?

How do you change the year?

 

[squirrellydw]

In the Calendar that I have, the space below each day number is just one cell. See image attached.

Is this the case with yours? YES

If one date cell has numerous entries do you want just that row to autofit or do you want the each row on the calendar to resize to match? Just that ROW

What is the expected maximum number of flights per day? It changes monthly so can't really give you a good answer

How do you change the year? On mine I just change the Year at the top

Thank for the help, really appreciate it