Date Range Search Across multiple tabs?

[rowan853]

Can anyone help?

What I'm trying to achieve.

I have an excel sheet which has an undefined number of tabs/worksheets (could be 50 and tomorrow 55).

I want to have a master page where i can enter a To and From date.
The code would then loop through every tab
For every tab where the date cell in column I is between my To/From date, return the full row in the master list (repeat)

*there may be tabs where there are multiple matches so it may return two rows from tab x

Any help would be greatly appreciated

 

[DanteAmor]

Update the following data in the macro:
- Set sh1 = Sheets("Master") 'Name of Master Sheet
- Case sh1.Name, "Report", "etc" 'These sheets will not be included

The From and To dates must be captured in A2 and B2
The data in the "Master" sheet should be like this:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Date From	Date To
2	04-jun	10-jun
3
4	Header	Header	Header	Header	Header	Header	Header	Header	Header	Header
5
Master

The results from row 5 down.

Try this:

Sub FiterDates()
  Dim sh As Worksheet, sh1 As Worksheet
  Dim lr As Long, lr1 As Long
  
  Set sh1 = Sheets("Master")    'Name of Master Sheet
  sh1.Rows("5:" & Rows.Count).ClearContents
  
  For Each sh In Sheets
    Select Case sh.Name
      Case sh1.Name, "Report", "etc"    'These sheets will not be included
      
      Case Else
        If sh.AutoFilterMode Then sh.AutoFilterMode = False
        lr = sh.Range("I" & Rows.Count).End(3).Row
        sh.Range("I1:I" & lr).AutoFilter 1, _
          ">=" & Format(sh1.Range("A2").Value, "mm/dd/yyyy"), xlAnd, _
          "<=" & Format(sh1.Range("B2").Value, "mm/dd/yyyy")
        
        lr1 = sh1.Range("I" & Rows.Count).End(3).Row + 1
        sh.Range("I2:I" & lr).EntireRow.Copy sh1.Range("A" & lr1)
    End Select
  Next
End Sub