Copying Autofiltered Results in VBA

[iancook1982]

hi,

I am using an autofilter to trim some results according to a specific criteria, i want to then copy this data to a sheet called "funds". then want to repeat this with the next criteria. I have the filter working fine. I just cannot seem to get this copying across to this new sheet.

For Each client In clientlist
With Worksheets("fundlist").Cells
.AutoFilter Field:=3, Criteria1:=client.Value

End With
Next client
Worksheets("fundlist").AutoFilterMode = False

clientlist = my list of criteria selection
I search through the fundlist sheet for these clients then copy the results to a sheet called funds.

Can you help?

Thanks,
Ian.

 

[Richard Schollar]

Try:

For Each client In clientlist
  With Worksheets("fundlist")
    .Cells.AutoFilter Field:=3, Criteria1:=client.Value
    .Autofilter.Range.Copy
    Worksheets.Add
    Range("A1").Pastespecial
  End With
Next client
Worksheets("fundlist").AutoFilterMode = False

 

[iancook1982]

I get an object required error '424'.

 

[Richard Schollar]

On which line?