Setting Criteria Range for Advanced Filter and Copy

[reberryjr]

I'm trying to filter a worksheet in one workbook, then paste the results into another workbook. I've managed to get auto filter to work, but in researching the approach this morning, it seems that the advanced filter function is more appropriate. I'm really struggling to understand how to set the actual filters. Meaning, if I want to filter on column U for values of "Coded" AND on column F for values of "Chelsea"; I haven't been able to find any examples on the web that outline how to do that.

Sub AssignChelsea1LPLoans()
Application.ScreenUpdating = False
Dim flp As Workbook
Dim mws2, flpws2 As Worksheet
Dim flpPath, flpFName As String
Dim mLastRow2 As Long
ThisWorkbookName = ActiveWorkbook.Name
Set mws2 = ThisWorkbook.Sheets("Active_Inv")
mLastRow2 = Sheets("Active_Inv").Range("U" & Rows.Count).End(xlUp).Row
SortActiveInvSheet mws2, "S1", "F1"
If mws2.FilterMode Then
    mws2.ShowAllData
Else
End If
Set flp = Workbooks.Open("[URL="file://\\crpjvffp00n2\BK"]File Location Here[/URL]")
Set flpws2 = ThisWorkbook.Sheets("Active_Inv")
mws2.Activate
Sheets("Active_Inv").Range("A1").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=flp.Sheets("Active_Inv").Range("A2"), Unique:=False
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

you cannot use the xlAnd operator with AdvancedFilter in VBA. You have to set up a criteria range to use multiple criteria. You can find several tutorials by typing 'VBA Advanced Filter Multiple Criteria' in the web search box and press enter.

 

[reberryjr]

I've searched using that verbiage, as well as numerous variations. It appears that I would have to actually set the criteria on the worksheet itself, which is not ideal. I guess I'll go the path of using auto filter, then copying the filtered results.

Thanks

 

[JLGWhiz]

I've searched using that verbiage, as well as numerous variations. It appears that I would have to actually set the criteria on the worksheet itself, which is not ideal. I guess I'll go the path of using auto filter, then copying the filtered results.

Thanks

If you only have two criteria, Autofilter works just as well, except you have to code in the copying step rather than have it done as part of the filterprocess as in AdvancedFilter. But if you have three or more cirteria, AdvancedFilter is great. It does take a little more effort to set up the filter, but the results are a real time saver, especially if you have hundreds, or thousands, of rows of data to filter.
Regards, JLG

 

[reberryjr]

Thanks! The autofilter is working, and I've got it coded to copy the desired records to the new workbook. The issue I'm running into now, is when the filter doesn't yield results. I don't want it to copy anything over. Still trying to navigate through that.

 

[reberryjr]

Figured that out.