change validation list value and copy and paste from dependent cells

[j.farr3ll]

Hi all,

This is quite hard to explain please see the attached workbook.

I have a sheet which autofilters according to a validation list in cell E2 using the code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If (Intersect(Target, Range("E2")) Is Nothing) _
   Then
      Exit Sub
   End If
   Cells.AutoFilter Field:=1, Criteria1:="=" & Range("E2")
End Sub

I have then used subtotal arrays to calculate the mean, median, max, min and total count for whichever values the filter shows.

What I want to do next is have a code which will select each possible option from the validation list (triggering the autofilter) and copy and paste the values from each dependent formula into a new sheet.

Any help would be greatly appreciated.

Many Thanks,
J.farr3lldummy 1.xlsm