Need macro excel to show data on change of heading

better

Deleted 

Your sample output isn't correct - it doesn't match Table 1 and Table 2, and the dates in B30:C30 are in July instead of in August.

But this should do what you want:

• Right-click the sheet tab of Sheet1.
• Select 'View code' from the context menu.
• Copy the code listed below into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim c As Long
    Dim v As String
    Dim d As Date
    Dim s As String
    Dim i As Long
    Dim r0 As Long
    Dim c0 As Long
    If Not Intersect(Range("A26"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Range("B28:D29").ClearContents
        Select Case Range("A26").Value
            Case "Table 1"
                c0 = 1
            Case "Table 2"
                c0 = 8
        End Select
        For r = 28 To 29
            For c = 2 To 4
                v = Cells(r, 1).Value
                d = Cells(30, c).Value
                s = ""
                i = 0
                For r0 = 3 To 7
                    If Cells(r0, c0 + 4).Value = v And Cells(r0, c0 + 5).Value = d Then
                        i = i + 1
                        s = s & vbLf & i & ". " & Cells(r0, c0 + 2).Value
                    End If
                Next r0
                If s <> "" Then
                    Cells(r, c).Value = Mid(s, 2)
                End If
            Next c
        Next r
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub