VBA Macro Copy Sheets to new workbook based on combobox filtered values

[Galdransxl]

Hi all,

I'm writing a code that once you click on one button, it:

1. Filters values in a table based on a combobox value (the first column of these filtered value is the name of my sheets)
2. Creates a new blank workbook named from the combobox & another cell in my workbook
3. Select all the sheets whose names are presents in the filtered value ("C" Column)
4. Copy Paste the selected sheets in the workbook created in 2
5. Saves and closes workbook 2
6. back to workbook 1 and removes filters

I can't seem to make 3) and 4) working, I either copy all sheets (regardless of the filter), either (current code) copies only the last one.

Please see the code below, thanks in advance!

I am struggling a lot with one issue, the code works fine until I want it to skip the hidden cells that are filtered... which gives me all the sheets to the new worksheet every time instead of having only the one filtered.

I put the dummy file here

Private Sub CommandButton1_Click()

Dim wb1 As Workbook, wb2 As Workbook, Filter As String, ExtractName As String, Version As String, I As Integer, Sheet_Name As String
Dim ReplaceSelection As Boolean

Dim rng As Range
Dim cell As Range

'--------------------- Starting the macro, this phase takes in account the desired filter and create a new workbook named accordingly with this filter -----------------------------
            
            Set wb1 = ThisWorkbook
            Filter = FilterExtract.Value 'captures the combobox field
            Version = Range("p3").Value
            ExtractName = Filter & " - Section_extract_" & Version
            Workbooks.Add.SaveAs Filename:=ExtractName
            'MsgBox (ExtractName)
            Set wb2 = ActiveWorkbook
            wb1.Activate
            I = 1
            
            
'---------------------- Now starting the Filter then Extract Sheets based on Filtered Value -----------------------



                    
        ActiveSheet.Range("$C$6:$N$300").AutoFilter Field:=12, Criteria1:=Filter 'filters based on what's in the box
    
    
             'Set rng = Range("C6:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
                    
                    Application.ScreenUpdating = False
                    ReplaceSelection = True
          

  With Range("C6:C300").SpecialCells(xlCellTypeVisible)
            
             Sheet_Name = Range("C6").Offset(I, 0) 'select the first filtered value from the table

 
                        While Sheet_Name <> "" 'while loop to navigate in the column with filtered sheets names until the cell is empty
                            
                                    Sheets(Sheet_Name).Select ReplaceSelection
                                    ReplaceSelection = False
                            
                                    I = I + 1
                                    Sheet_Name = Range("C6").Offset(I, 0)
                            
                        Wend

 
  End With
    
        ActiveWindow.SelectedSheets.Copy Before:=wb2.Sheets(1) 'copy all the sheets selected to the new workbook


                    Application.ScreenUpdating = True

                
'--------------------- End of the code, algorithm returns to initial workbook and removes filters ---------------
                
'--------------------- End of the code, algorithm returns to initial workbook and removes filters ---------------
            
              wb2.Close SaveChanges:=True
              wb1.Activate
              wb1.Sheets("STATUS").Activate

                   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData 'removes filter from STATUS sheet
                  
             MsgBox ("Extract done, please find the workbook " & ExtractName & " in your documents.")
            
End Sub