Excel help

[PCDave]

ITs been a while since I have used Excel and cannot quite remember how to do this.

I have multiple sheets with data of the same value

e.g. sheet 1 columns- id - name - status
sheet 2 coloumns - id - name - status

etc

On the last sheet I want to pull all the rows from each sheet that match the status criteria of "yes" into one sheet so I can review them all.

Does anyone know a good way to do this?

 

[PCDave]

You can use a filter, and then just copy your results onto the other sheet if you want?

Or are you wanting a solution which will update itself?

A Solution which would update itself would be preferred. I have filters on them already if I need to find the data out manually but just as a small project want to get all the info I need in one place instead of going to about 15 different sheets each time and running filters...any ideas?

 

[PCDave]

I have found a formula which achives what I want but I am not sure how to configure it to work in mine.

Sub Filter()
    With Sheet1
'
        .Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range( _
        "Criteria"), Unique:=False

        .Range("Data").SpecialCells(xlCellTypeVisible).Copy Destination:="PasteRange"
        .ShowAllData
    End With
End Sub

I know where it says "data" and "criteria i need to enter my values but I am unsure of what to put in. Any ideas?

 

[PCDave]

Thanks for your help. It does unfortunately otherwise I would post it here.

A B C D E F
------------------------------------------------------------------------
1 ID Name Status ID Name Status
-------------------------------------------------------------------------
2 1 drink received Received
3 2 Food ordered Ordered
4 3 beer not ordered Not Ordered

Above I have made a mock spreadsheet to try and help me understand

Would the formula look something like this?

Sub Filter()
    With Sheet1
'
        .Range("A1:C4").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range( _
        "D1:F4"), Unique:=False

        .Range("A1:C4").SpecialCells(xlCellTypeVisible).Copy Destination:="sheet2!$a$1:$c$4"
        .ShowAllData
    End With
End Sub

 

[PCDave]

Thanks to both of you for your help. With a bit of modding to the formula above I now have it working.