Filtering data on multiple creterias

[Veeru]

Hi,

I am lookng for a code which select filter data on multiple creteria on first sheet and then paste it in last sheet

Filter creteria are ABC and all dept mentioned in col C in first sheet

Sample file attached

[mana]

Option Explicit

Sub test()
Dim r As Range
Dim d As Range
Dim c As Range
Dim b As Range

Set r = Worksheets("Data").Cells(1).CurrentRegion
Set d = Worksheets("FInal Sheet").Cells(1, 2).Resize(, 15)

Set c = Worksheets("Selection").Cells(1).CurrentRegion
On Error Resume Next
Set b = c.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not b Is Nothing Then b.FormulaR1C1 = "=r[-1]c"

r.AdvancedFilter xlFilterCopy, c, d

b.ClearContents

End Sub

[Veeru]

Thank you working fine on attached file but i have different data and when i tried to change it but it didnt work...Could you please look at it....

[mana]

Option Explicit

Sub test()
Dim r As Range
Dim d As Range
Dim c As Range

Set r = Worksheets("Data").Cells(1).CurrentRegion
Set r = Intersect(r, r.Offset(1))
Set d = Worksheets("Working").Cells(1)
Set c = Worksheets("Selection").Cells(1).CurrentRegion

r.AdvancedFilter xlFilterCopy, c, d

r.Rows(0).Copy
d.Insert

End Sub

[Veeru]

Thanks agian...but this code is pasting every CC data in last tab...where we want only 102 cc data aong with all other elemets mentioned in col. B in first tab.

[Mati44]

Hi Mana, Is it possible to make this code accept more IDs, names and Depts ? I don't want to interfere with Veeru's post, but can you answer me too after Veeru's got what he needed. thanks. Another question, if you don't mind, can this be done in one sheet instead of three separate sheets?

[Bob Phillips]

Mati44, it would be better for you to start your own thread and put all the details of your problem there. You have more chance of an answer that way.

[Mati44]

sorry about that. thanks i will do that.

[Veeru]

However i didn't get what i want...i m still looking for solution...