Loop through subset of worksheets

[Oraekene]

Hi. Good day. I would like to repeated loop through a set counter of worksheets and paste the results from each group in a different sheet

For example, in this sheet Sample sheet i have 12 sheets with data and 4 other sheets. I would like to skip those first four sheets, copy and combine the already filtered data of the first 6 data sheets into an empty sheet that can be called results sheet 1, then do the same for the next 6 data sheets and copy into another empty sheet that can be called results sheet 2. Data sheets will continually be added to the workbook so i'd the script to dynamically work across the sheet count and dynamically create the results sheets too as needed. Here is the initial code i tried

Sub trymyluck()
Dim i As Long
Dim c As Long
Dim j As Long
Dim ws As Worksheet



For c = 5 To Sheets.Count Step 6
    For i = 1 To 5
        j = c + i
        Sheets(j).Range("A1:A").SpecialCells(xlCellTypeVisible).Copy Destination = Sheets("analysis").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
        
    Next i
Next c
End Sub

As you can see, it didn't quite work. Would appreciate some help on this

 

[DanteAmor]

Try this:

Sub subset_of_worksheets()
  Dim i As Long, m As Long, n As Long
  Dim sName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  sName = "results "     'New sheet name prefix
  For i = Sheets.Count To 1 Step -1
    If Sheets(i).Name Like sName & "*" Then
      Sheets(i).Delete
    End If
  Next
  
  m = 6                 'Set of 6 sheets
  For i = 5 To Sheets.Count
    If m = 6 Then
      m = 0             'and destination column
      n = n + 1         'results sheet number
      Sheets.Add(, Sheets(Sheets.Count)).Name = sName & n
    End If
    Sheets(i).Range("A:A").SpecialCells(xlCellTypeVisible).Copy Sheets(sName & n).Cells(1, m + 1)
    m = m + 1
  Next

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub