Option Explicit

Sub Demo()
    Dim ws As Worksheet, mainSht As Worksheet
    Dim rFind As Range, rDate As Range, rSht As Range
    Dim iDate, arrDate, arrSht
    Dim DateCnt As Long, ShtCnt As Long
    Dim i As Long, iRow As Long, iCol As Long
    Const KEYWORD = "Closing"
    Set mainSht = Sheets("Control")
    ' Load date and sheet name
    With mainSht
        arrDate = .Range("C3", .Cells(3, .Columns.Count).End(xlToLeft)).Value
        DateCnt = UBound(arrDate, 2)
        arrSht = .Range("B5", .Cells(.Rows.Count, 2).End(xlUp)).Value
        ShtCnt = UBound(arrSht)
    End With
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Control" Then
            ' Search KEYWORD
            Set rFind = ws.Cells.Find(What:=KEYWORD, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                iDate = ws.Range("E4").Value
                ' Validate E4 is Date
                If IsDate(iDate) Then
                    iRow = 0: iCol = 0
                    ' Match date in row 3
                    For i = 1 To DateCnt
                        If arrDate(1, i) = iDate Then
                            iCol = i + 3
                            Exit For
                        End If
                    Next i
                    If iCol > 0 Then
                        ' Match sheet name in Column B
                        For i = 1 To ShtCnt
                            If arrSht(i, 1) = ws.Name Then
                                iRow = i + 4
                                Exit For
                            End If
                        Next i
                        If iRow > 0 Then
                            ' Populate Control sheet
                            mainSht.Cells(iRow, iCol).Value = rFind.Offset(0, 1)
                        End If
                    End If
                End If
            End If
        End If
    Next ws
End Sub

字符串