Using Mutiple inputs from data validation lists to seach sheet and return multiple records on another.

[mterhaar]

Hi There,

First time posting so I hope its not a silly question! After spending a bit of time looking for some solutions for this i've come up stuck. Basically I want to manipulate the filter options on one sheets using data validation lists on another (which are generated on startup using VBA to search each column and return unique results) and then return all the results into a seperate page.

I have tried to use the following code located in a seperate module but it returns an error: "The extract range has a missing or illegal field name"

Sub FilterThem()

FinalRow = Sheets("Data").Range("A1").End(xlDown).Row

Sheets("Reporting").Range("E3:O1000").Clear

Sheets("Data").Range("A2").Resize(FinalRow, 10).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Reporting").Range("B3:C12"), _
CopyToRange:=Sheets("Reporting").Range("E3:O10"), _
Unique:=False

End Sub

I get the impression this is because of how my criteria range is setup. Its layed out as below:

Consultant | *Consultant Name Here*
Agent Number | *Agent Number Here*
Etc..

Say it has the criteria in A1 and the input in B1.

In some tutorials I have seen the criteria with the heading and the top and the input directly under it (So criteria in A1 and input in A2)

Or does this method not a allow for partial inputs, for instance if I only wanted to filter by consultant and not by agent as well, would this error?

Any ideas?