How to split workbook into separate files based on sheet names?

user_12291034 0
Viewed 138

I want to save sheets as separate files, but only for sheet names which start with AB***.

The code below does it for all sheet names.

Sub SplitEachWorksheet() 
  Dim FPath As String 
  FPath = Application.ActiveWorkbook.Path 
  Application.ScreenUpdating = False 
  Application.DisplayAlerts = False 
  For Each ws In ThisWorkbook.Sheets 
    ws.Copy 
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" 
    Application.ActiveWorkbook.Close False 
  Next 
  Application.DisplayAlerts = True 
  Application.ScreenUpdating = True 
End Sub
vba excel
1 Answers

Assuming the rest of it works, here is how to check the name first.

Dim ws as Worksheet
For Each ws In ThisWorkbook.WorkSheets 
  If Left$(ws.Name, 2) = "AB" Then ' <--- added an IF statement
    ws.Copy 
    Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" 
    Application.ActiveWorkbook.Close False 
  End If
Next
user_84229530
Related