using autofilter data

  • Thread starter Thread starter edward
  • Start date Start date
E

edward

Is it possible to get access to data appearing on the auto filter dropdown
lists which show at the top of the filtered range?
Would it be possible to control filtering operation from data validation
dropdown lists put on a different worksheet than that containing the
filtered range?
 
You could do it with VBA (macros). A quick example would be to record
yourself using the autofilter. Then use Alt+F11 to bring up the coding in one
of the modules. You'll see that the VBA calls out an autofilter criteria. You
could program this criteria to be the value of any cell, including a cell
that has a data validation dropdown.
 
Here's one I did recently. Adapt to suit your dropdown.

Sub filterandcopy()
For i = 1 To 3
dlr = Sheets("sheet13").Cells(Rows.Count, "b").End(xlUp).Row + 1
slr = Cells(Rows.Count, "a").End(xlUp).Row
With Range("A1:d" & slr)
..AutoFilter Field:=1, Criteria1:=i
..Offset(1).Copy Sheets("sheet13").Cells(dlr, "b")
..AutoFilter
End With
Next i
End Sub
 
Back
Top