Hi everyone, hope you're all safe and well!
I'm writing a code that once you click on one button, it:
- Filters values in a table based on a combobox value (the first column of these filtered value is the name of my sheets)
- Creates a new blank workbook named from the combobox & another cell in my workbook
- Select all the sheets whose names are presents in the filtered value ("C" Column)
- Copy Paste the selected sheets in the workbook created in 2
- Saves and closes workbook 2
- 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!
Code
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
'--------------------- Starting the macro, this phase takes in account the desired filter and creates a new workbook named accordingly with this filter -----------------------------
Set wb1 = ThisWorkbook
Filter = FilterExtract.Value 'captures the combobox field
Version = Range("p3").Value
ExtractName = Filter & Version
Workbooks.Add.SaveAs Filename:=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
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
Application.ScreenUpdating = False
Sheets(Sheet_Name).Select
I = I + 1
Sheet_Name = Range("C6").Offset(I, 0)
Wend
ActiveWindow.SelectedSheets.Copy Before:=wb2.Sheets(1) 'copy only the sheets selected according to filter to the new workbook
Application.ScreenUpdating = True
'--------------------- 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
End Sub
Display More