Search Multiple Tabs

[Medusa501]

I need help finding an equation to search through multiple tabs (8) that will have multiple rows based on finding dates that are equal to or less then the last day of the current month. The data columns all have same formatting in each tab. When a date is found that meets the requirement, all data on that row should be returned (cells B - M). There will be multiple rows meeting this requirement through out the search.

Names of of 2 tabs are Hamilton and Philly
Data in each tab is B6 - M125


Thought there might be a IF statement and VLookup combination to do this, but open to other possibilities.


Thank you,

 

[ouvay]

I can provide you with a vba solution as I've forgotten the bulk of all the formulas I once knew.. VBA comes much more easier to me
Which column is the date in？

Where do you want the result to go? -- the data that meets the criteria

to be honest, I haven't tested or double checked this so I doubt it will work right away... but with the information I have... assuming that the dates are in column B

Give this a go... the data that meets the criteria will show up in a new workbook

I have put notes in the code to help you understand it and change it if need be...

Sub conjureData_Medusa501()

    Dim sh As Worksheet, lr As Long, data As Variant, match As Variant, i As Long, n As Long, col As Long, strDate As Date

    ReDim match(1 To 2000, 1 To 12) As Variant

    For Each sh In ThisWorkbook.Worksheets

        lr = sh.Cells(Rows.Count, 2).End(3).Row          ' here we are checking if you have data after row 125

        data = sh.Range("B6:M" & lr).Value               'I know you said its only until m125.. but Better to be safe incase you have changes in the future

        For i = LBound(data) To UBound(data)

            strDate = DateValue(data(i, 1))                 '''''' Change the date column here -- Currently column B = 1... C = 2... D = 3... so and so forth := data(i,1) = data(i,B) please change 1 to whatever column the dates are in

            If Format(strDate, "dd") <= Format(DateSerial(Year(Now), Month(Now), 0), "dd") Then

                n = n + 1

                For col = LBound(data, 2) To UBound(data, 2)    ' copying matched criteria

                    match(n, col) = data(i, col)

                Next col

            End If

        Next i

    Next sh

 

    With Workbooks.Add                                      'creating new book since I don't know where you want this information to go

        .Worksheets(1).Range("B6").Resize(n, UBound(match, 2)).Value = match

    End With

End Sub

If you are unfamiliar with how to use VBA..

Follow these steps

-Press Alt+F8 to open your VBA editor
-Find your workbook on the left side in the Project window
-Expand your workbook project
-Double click on ThisWorkbook
-paste the code and press F5