How can i auto filter on 2 columns with Multiple criteria and then copy columns to different sheet

[simmybehl]

Hi Team,

I am using below macro to auto filter on col D and Col F on worksheet "confirms" with below criterias and then trying to copy col P and Col S filtered data to another worksheet names "Non=Cash Trades".

the below code is autofiltering only col D with "N" and Col F with "COD" not "F/D" and copy function is not working at all. Please help.

Sub Copy()
Dim i As Long, LR As Long
Dim sht, sht1 As Worksheet


Set sht = ActiveWorkbook.Worksheets("confirms")
Set sht1 = ActiveWorkbook.Worksheets("NON-CASH Trades")


sht.Range("A:S").AutoFilter Field:=4, Criteria1:=Array("N")
sht.Range("A:S").AutoFilter Field:=6, Criteria1:=Array("F/D", "COD")
sht.Range("P2:P5000").Copy
sht1.Range("A4:A5000").PasteSpecial xlPasteValues
sht.Range("S2:S5000").Copy
sht1.Range("C4:C5000").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Fluff]

Hi & welcome to MrExcel
Try

sht.Range("A:S").AutoFilter 4, "N"
sht.Range("A:S").AutoFilter 6, Array("F/D", "COD"), xlFilterValues

For the filter

 

[simmybehl]

Hey, thanks so much!!

yes the autofilter does work now exactly the way i want.

Request you to please shed some light on the copy code as well, why isnt it working..

 

[Fluff]

Please define "Isn't working"

 

[simmybehl]

i mean the below, doesn't work, like i need macro to copy cells of col P & col S after placing the above auto filter to the another worksheet starting from col A row 4 and col C starting from row 4 respectively

sht.Range("P2:P5000").Copy
sht1.Range("A4:A5000").PasteSpecial xlPasteValues
sht.Range("S2:S5000").Copy
sht1.Range("C4:C5000").PasteSpecial xlPasteValues
Application.CutCopyMode = False

 

[Fluff]

In what way "Doesn't it work"?
Do you get error messages?
Does it copy everything?
Does it copy nothing?
Or what?

 

[simmybehl]

oh my bad, it is working, thank you so much for looking into this!!!

 

[Fluff]

Glad it's sorted & thanks for the feedback