Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Date Range Search Across multiple tabs?

0

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

Answer
Discuss

Discussion

John,

You have already done enough and i don't want to trouble you further but do you know where i might find some guidance to try and extend this code a little further?

So i may return the whole row + cell A1 + a link to that particular tab.

Thank you
rowan853 (rep: 4) Jun 22, '22 at 3:12 am
Rowan. Thanks for selecting my answer. I can do that but it maybe tomorrow or after so you might be better asking a new question. You need to attach your file (but no personal data) and explain what you mean-  can the extracted data row be shifted one cell right abd the sheet name added to column A? Or do you mean you need a hyperlink to the sheet (or row)? Those things are increasingly involved BTW. 
John_Ru (rep: 3992) Jun 22, '22 at 4:13 am
Rowan

Please see the Correction added to my answer (and revised file).

Kindly ask a new, second question _ I have a draft solution which added hyperlinks in column A which take you to the sheet/ matching date entry. SHould be able to reply tomorrow.
John_Ru (rep: 3992) Jun 22, '22 at 5:09 pm
Add to Discussion

Answers

0
Selected Answer

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.

Discuss

Discussion

John you are a legend...

You have made my day and i can't thank you enough!

Amazing

Thank you :)
rowan853 (rep: 4) Jun 21, '22 at 12:53 pm
Thanks Rowan. Kindly mark my answer as Selected (for the guidance of others and to enhance our reputations on this Forum)
John_Ru (rep: 3992) Jun 21, '22 at 1:33 pm
thanks again
rowan853 (rep: 4) Jun 23, '22 at 2:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login