Loop through autofilter

[henrik2h]

Hi
I have a table with autofilters. For column A I would like, in VBA, to choose each item one by one until all have been looped through. Is it possible? i.e. choose number one item in the autofilter-list "colA" (without knowing the name of the item), I then do some copy paste of those filtered values to another sheet, next number two on the autofilter-list is choosen and the corresponding filtered values are copied to another place and so on.

Any ideas?

 

[Worf]

Hi

See if this helps:

Sub Sweden()
Dim r%, sh As Worksheet, lastrow%, lc%, i%
Set sh = Sheets("Sheet3")                                           ' source sheet with table at a1:d18
sh.Range("h1") = sh.Range("a1"): sh.Range("h2") = ""                ' the table need headers for advanced filtering
sh.Range("a1:a18").AdvancedFilter xlFilterCopy, sh.Range("h1:h2"), sh.Range("i1"), True ' list items at column I
sh.Activate
lastrow = Evaluate("=match(lookup(2,1/(i:i<>""""),i:i),i:i)")       ' end of column A items, at column I
Sheets("Sheet2").Activate                                           ' destination sheet
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For r = 2 To lastrow                                                ' copy to destination
    sh.Range("h2") = sh.Range("i" & r)                              ' criterion
    sh.Range("a1:d18").AdvancedFilter 2, Range("Sheet3!Criteria"), Cells(1, lc + 5 * r), False
Next
End Sub

 

[henrik2h]

Thank you for your efforts. I had to solve it another way but I will try your solution whenever I get the chance.