CODEWITHSUNDEEP
excelvba
Raj
Raj

Reputation: 17

Data extract from one sheet to another sheet with multiple criteria in excel vba

I have large data with 6 columns in sheet 2. I would like to extract the data from sheet 2 to sheet 5 based on multiple criteria.
my sheet 2 data ...

Example data

so, i write following code to extract data from sheet 2 to sheet5.

Sheet2.Range("a1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=Sheet5.Range("a3:d3"), _
CopytoRange:=Sheet5.Range("a10:k10"), Unique:=False
'Columns(6).AutoFit

problem is when I run above code it displays all data from sheet 2, but it is not met with my criteria.

for example if user selects following details... Place : USA

Month : Jul-19

Variety : I

remarks : PR

output should be like these... Example Output

Upvotes: 0

Views: 680

Answers (1)

JvdV
JvdV

Reputation: 75860

The CriteriaRange is expecting headers AND criteria, furthermore the CopyToRange will work assigning it just a single cell (you wouldn't know pre-filtering the amount of rows it should contain with each filtering). So with the following setup on sheet1 and sheet5:

Sheet1:

enter image description here

Sheet5:

enter image description here

Running the following code:

Sub AdvancedFiltr()

Sheet2.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet5.Range("A1:F2"), CopyToRange:=Sheet5.Range("A4")

End Sub

Results in Sheet5:

enter image description here

Some remarks:

  • CriteriaRange: The headers need to match the headers from the database exactly.
  • CriteriaRange: You don’t need to have every header, just the ones you want to filter by.
  • CopyToRange: It's wise to clear the range on Sheet5 before you apply another filter.
  • I really think this is a great article on AdvancedFilter to make clear how it works.

Upvotes: 1

Related Questions