Loop through filtered values and copy the filtered array values to another sheet

[krshnn]

I'm basically a noob when it comes to looping in excel.
I find the below issue seems to be more of a loop point. Kindly assist. Thanks in advance.

I have 22 columns with variable rows.
Here the 16th column needs to be filtered. 16th column name is "Status"
Say for example, Status column usually contains values of (for example) "Documents submitted to bank" , "Documents not submitted" ,"Payment not realized etc."

So what I would like to achieve is
1.Apply autofilter first -> Range("A1:V1").autofilter
2. Filter Status column for each value in that column like the above three example criteria stated above
3. Then copy visible cells (including header and all rows of that filtered value - basically variable rows by 22 columns) to a new sheet.

In case of any queries, kindly ask. Thanks in advance

 

[Peter_SSs]

Give this a try with a copy of your workbook.
Assumes column Z is vacant to use as a helper column.

Sub CopyToSheets()
  Dim r As Long
  
  Application.ScreenUpdating = False
  With ActiveSheet.Range("A1").CurrentRegion
    .Columns(16).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z1"), Unique:=True
    For r = 2 To .Range("Z" & Rows.Count).End(xlUp).Row
      .AutoFilter Field:=16, Criteria1:=.Range("Z" & r).Value
      Worksheets.Add After:=Sheets(Sheets.Count)
      .Copy Destination:=Range("A1")
    Next r
    .AutoFilter Field:=16
    Intersect(.EntireRow, .Parent.Columns("Z")).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[krshnn]

Thanks Sir for your valuable time and effort. It works as intended.

 

[Peter_SSs]

You're welcome. Thanks for the feedback.