PDA

View Full Version : Filtering data on multiple creterias



Veeru
04-23-2018, 02:00 AM
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
04-23-2018, 05:24 AM
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
04-23-2018, 09:56 PM
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
04-24-2018, 03:53 AM
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
04-24-2018, 09:13 PM
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
04-30-2018, 01:01 PM
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
04-30-2018, 03:44 PM
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
04-30-2018, 11:06 PM
sorry about that. thanks i will do that.

Veeru
05-02-2018, 03:39 AM
However i didn't get what i want...i m still looking for solution...