Date Range Search Across multiple tabs?

Hi Rowan and welcome to the Forum.

Don't normally do this (we answer questions, not deliver mini-projects) but the attached should help / fix your problem...

In the attached file, I've created a macro which runs from a sheet (currently) called "Master". It has 3 other sheets (from demo purposes) but you could replace these with your 50 or however many sheets- though that might slow it down (but there are ways to counter that).

Data validation is added to cells B2 (so it has to be a date > 01/01/2020) and B3 (it has to be a date greater than of equal to that in B2). Provided you add dates to both then click the button "Search other sheets", the code will loop through all other sheets and extract any rows where there's a matching date in column I.

E.g. it leaves me with a report between 01 June and 15 June 2022 but change B3 to 30 June and it will refresh with more records.

I've commented the code so you can see what's happening:

Sub Button1_Click()

' ### TeachExcel

Dim ws As Worksheet, LastI As Long, n As Long
Dim LastRow As Long

' clear master sheet except first 7 rows
Sheet1.UsedRange.Offset(6, 0).Delete

' Determine last used row in sheet 1
LastRow = Sheet1.Range("I" & Rows.Count).End(xlUp).Row + 1
If LastRow < 7 Then LastRow = 7
' get user dates
FirstDate = Sheet1.Range("B2").Value
Lastdate = Sheet1.Range("B3").Value

'Just tell user if dates need adding
If FirstDate = "" Or Lastdate = "" Then
    MsgBox "Please add valid dates to both cells B2 and B3"
    Exit Sub
End If

' Loop through sheets
For Each ws In ThisWorkbook.Worksheets
    ' unless it's the master sheet..
    If ws.Name <> Sheet1.Name Then
        'Find last used row in column I of that sheet...
        LastI = ws.Range("I" & Rows.Count).End(xlUp).Row
        ' and loop through column I starting at row 2:
        For n = 2 To LastI
            With ws.Cells(n, "I")
                If IsDate(.Value) And .Value >= FirstDate And .Value <= Lastdate Then
                    ' if date's in range, copy /paste to master sheet
                    .EntireRow.Copy Sheet1.Rows(LastRow)
                    'Increment row counter for next match
                    LastRow = LastRow + 1
                End If
            End With
        Next n
    End If

Next ws

' State (in A5) what the sheet now reports and tell user
Sheet1.Range("A5").Value = "Retrieved data matching above dates: from " & FirstDate & " to " & Lastdate
MsgBox "Done! Extracted " & LastRow - 7 & " matching rows"

End Sub

Correction:

The code above originally read:

' clear master sheet except first 7 rows

Sheet1.UsedRange.Offset(7, 0).Delete

but without anything in row 1 (as originally), it left the first two lines of data uncleared (though they got overwritten if valid data was found). The attached file corrects both things.

Hope this helps/ makes sense.