excel - Looping through all available autofilter criteria one at a time in vba -

I was wondering if there was a way to get all the different AutoFilter criteria in one list for each criteria , in the copy of the end and pasting each separate table which appears as a separate sheet such as it is turned on.

Ideally it will run n times:

  ActiveSheet.Range (AllRows) .AutoFilter field = 10, Criterion 1: = CritVariable   
 Where N is the number of different variables.  
 I want to emphasize that how to copy and paste in the macro, but I was curious how to repeat all the different norms because the norms vary on the basis of day May be. If a list is not available then how would I go through the criteria again?   
 
 
 You can  study and customize   
 
 I have an employee table in column J, starting with cell A5 with a list of office;  
 I  
 I  deleting duplicates  from below category W1;  
 Then I copy from G5 to the bottom (does not consider any blank space in this column's data);  
 The filtered data is taken (cut) until the new worksheet, which is named after the current office name (criteria);  
 After each advanced filter, cell W2 is removed, thereby increasing the value in W3, so that it can be used for next filter operation.   
 This means that when you press Ctrl-End to go to the last-used cell, then more is required. You can find a way to resolve the matter if necessary;)  
  sub SheetsFromFilter () Dim wsCurrent Worksheet dim wsNew Worksheet dim iLeft integer set wsCurrent = ActiveSheet Application.ScreenUpdating = false as for as the range (.. "G5", range ( "G5") and (xlDown)) copy range ( "W1") range ( "W1") CurrentRegion.RemoveDuplicates column: .. = 1, Header: = xlYes iLeft = Range ("W1") .coreant region. ROSCount - 1 while iLeft & gt; 0 wsCurrent.Range ("A5") CurrentRegion.AdvancedFilter xlFilterCopy, _wcsCurrent.Range. ("W1: W2"), wsCurrent.Range ("Z1") set wsNew = Worksheets.Add wsCurrent.Range ("Z1"). CurrentRegion.Cut wsNew.Range ( "A1") wsNew.Name = wsCurrent.Range ( "W2") value wsCurrent.Range ( "W2") Delete xlShiftUp iLeft = iLeft - .. 1 loop wsCurrent.Range ("W1"). Skip application. ScreenUpdating = True End Sub   
 BTW I do not intend to modify it for my specific file; It's something you should do (or give it to ;;)).  
  BTW  This can be done using the normal (rather advanced) filter. You still copy the columns and delete the duplicates.  
  Added : Well, I felt motivated to get myself also with Autofilter:  
  sub SheetsFromAutoFilter () as dim wsCurrent Worksheet set dim wsNew worksheet integer as dim iLeft wsCurrent = ActiveSheet Application.ScreenUpdating = false range ( "G5", range ( "G5"). End (xlDown )) In form of. Copy range ("W1".) Range ("W1") CurrentRegion.RemoveDuplicates column: = 1, Header:. = XlYes iLeft = Range ("W1") CurrentRegion.Rows.Count - 1 while Do iLeft & gt; 0 set wsNew = Worksheets.Add wsCurrent.Range ("A5") CurrentRegion .With the AutoFilter area: .. = 7, _ Criteria1: = wsCurrent.Range ( "W1") Offset (iLeft) .Value .Copy wsNew.Range ( "A1") offset wsNew.Name = wsCurrent.Range ( "W1") with Kotofiltr End (ILeft). Value iLeft = iLeft - 1 loop wsCurrent.Range ("W1"). CurrentRegion.Clear application.ScreenUpdating = True last sub   
 [Both processes can be improved by using defined names and some error handling / probes.]