vba - excel indirect function to read dates and return dynamic values

Question

looking through the forum and can't find what I need. I have 100+ sheets with unique sheet names and data in column B. Column B will contain various END DATES. some sheets will have 1 or 2 end dates, others have upwards of 30 end dates. . I would like to create a summary page containing a table that will update itself to show all sheet names that have END dates in column B expiring within the next 30 days. is this something that requires coding? or use of the excel indirect formula with maybe a vlookup wrapped around it?

Answer 1

Private Sub ChkEndDates()
    Dim ws As Worksheet
    Dim cell As Range
    Dim rowCount as Long
    For Each ws in Worksheets
        If ws.Name <> '"summary page" Then
            rowCount = WorksheetFunction.CountA(ws.Range("B:B"))
            For i = 1 To rowCount
                If (ws.Cells(i, 2).Value - Date < 30) And (ws.Cells(i, 2).Value > Date) Then
                    MsgBox ws.Name
                    'And maybe push into the summary page
                End If
            Next i
        End If
    Next ws
End Sub