Autofilter with a Criteria and copy individual columns to sheet 2 and sheet3 using VBA

[Vijayonline2008]

Name Tax1Amount
Sam 74 49
Jose 77 74
Sam 17 21
JOse 24 85
JOse 55 66
I have three columns like this in sheet 1. Want to Filter name wise and only copy the Amount column of first name Sam to Sheet 2 A column and second name Jose to sheet3 A column. Required a macro for that.

Results should be like this.
In Sheet2 A column
49
21

In Sheet3 A column
74
85
66
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

Something like this.

Sub FilterCopyCol()
If Worksheets("Sheet1").AutoFilterMode Then Worksheets("Sheet1").AutoFilterMode = False

With Worksheets("Sheet1").Cells(1).CurrentRegion
    .AutoFilter Field:=1, Criteria1:="Sam", Operator:=xlFilterValues
    .Offset(0, 2).Resize(, 1).SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Cells(1, 1)
End With

If Worksheets("Sheet1").AutoFilterMode Then Worksheets("Sheet1").AutoFilterMode = False

With Worksheets("Sheet1").Cells(1).CurrentRegion
    .AutoFilter Field:=1, Criteria1:="Jose", Operator:=xlFilterValues
    .Offset(0, 2).Resize(, 1).SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Cells(1, 1)
End With

End Sub

 

[Vijayonline2008]

Thanks for the help, it was really helpfull