Filter Data Across Multiple Sheets

Re: Filter Data Across Multiple Sheets

What you ask could be done, but I think a better solution would be to use an Access (or other) database to hold your data and then retrieve as needed into Excel for further manipulation as required.

Re: Filter Data Across Multiple Sheets

Or stop spreading data over multiple sheets.

Re: Filter Data Across Multiple Sheets

Hi Jamie, I think the point here is that you have too much data for Excel. You need something more suited for large volumes of records. i.e. a proper database

Too much data in the wrong type of "container".... as you see from your current problem, it becomes difficult to manage/maintain/manipulate. And Excel will also get terribly slow.

Post a small sample - the code itself is not rocket science. If I get a chance before I leave today, I might take a bang off it.

Ger

Re: Filter Data Across Multiple Sheets

This is by no means finished - needs a lot more testing. Also, dont know how it will respond to so much data like you have...

I have assumed you were only searching for Scheme Number (Column G).

I copied data into 4 sheets just to try and replicate the "multi-sheet" filter scenario....

Wont be around again until Wednesday.

You could sexify it by putting in error and informational messages for when no records are found and offering more fields to filter by....

HTH
Ger

Re: Filter Data Across Multiple Sheets

Perhaps a job for Access.

Re: Filter Data Across Multiple Sheets

Hi Ger:
your code run perfectly for my problem but instead of user form i have a list of parts in data sheet in column A. i want to search same thing by that list instead of entering data into user form.
Thanks in advance.

Re: Filter Data Across Multiple Sheets

Hi Raj,

Using the EXACT SAME data as in the attachments then this slight adjustment works by taking the value of the SELECTED cell and filtering the columns based on that value. Since this thread is 8 years old, please start a new thread if this code does not work.

Public Sub FilterCopyRows_SelectedCell


Dim ws As Worksheet
Dim My_Range As Range
Dim strValue As String


'speed things up
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'CHANGE THIS LINE
strValue = Selection.Text 'change this to whereever your value is that you want to filter.
'*****************


'create results sheet
On Error Resume Next: Worksheets("Results").Delete: On Error GoTo 0:
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Results"
Worksheets(1).Rows(1).Copy Destination:=Worksheets("Results").Rows(1)


For Each ws In Worksheets
    If ws.Name <> "Results" Then
        With ws
            Set My_Range = .Range("A1:M" & .Range("A" & .Cells.Rows.Count).End(xlUp).Row)
            'filter each sheet
            
            My_Range.AutoFilter Field:=7, Criteria1:=strValue 'assumes "Scheme" in column "G" is what you want to filter on (G=7)
            
            'copy filtered rows
            My_Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy _
                Destination:=Worksheets("Results").Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0)
                
            'turn off autofilter
            My_Range.AutoFilter
        End With
    End If
Next


Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


Unload UserForm1


Worksheets("Results").Activate


End Sub

Regards
Ger