Hi David,

Setting your criteria range to:

Set RngCrit = .Range("A1").CurrentRegion '

means that the filter criteria will be overwritten by your extracted data.

You should, therefore move the criteria range away from your output range.

Additionally, you should clear your output range between filter operations.

Using H1 on the criteria file sheet as the anchor cell for the criteria
range, your code might read as:

Sub Tester()
Dim rngData As Range, RngCrit As Range
Dim rngOutput As Range

With ActiveWorkbook
With .Sheets("source data")
Set rngData = .Range("A1").CurrentRegion '
End With

With .Sheets("criteria file")
Set RngCrit = .Range("H1").CurrentRegion '
Set rngOutput = .Range("A1")
End With

.Sheets("criteria file").Activate
rngOutput.CurrentRegion.ClearContents
rngData.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=RngCrit, _
CopyToRange:=rngOutput, _
Unique:=True
End With

End Sub

Change H1 to a convenient location non-contiguous to the output range.

---
Regards,
Norman



"david shapiro" wrote in message
...
Hi,

A bit urgent - Does anyone know why this isn`t working? The code is to
do an advanced filter taking the full range of data in a worksheet? If
possible, I`d like to alter it to put the results of the filter in a new
sheet. Thanks.

Dave

"source data" - the main file which is being filtered from.
"criteria file" - the criteria for the filter


Dim rngData As Range, RngCrit As Range
Dim rngOutput As Range

With ActiveWorkbook
With .Sheets("source data")
Set rngData = .Range("A1").CurrentRegion ' End With

With .Sheets("criteria file")
Set RngCrit = .Range("A1").CurrentRegion '
Set rngOutput = .Range("A1")
End With

.Sheets("criteria file").Activate
rngData.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=RngCrit, _
CopyToRange:=rngOutput, _
Unique:=True
End With


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!