Guide to Splitting Sheets in Excel into Separate Files Using VBA

If your Excel file is overloaded with too many worksheets, you might consider splitting the worksheets into separate files. In this article, we will show you how to split Excel worksheets into separate files using VBA.

1. Why Split Sheets into Separate Files?

When working with extensive datasets in a single workbook, Excel may experience slow performance or even crashes. Splitting the sheets can:

• Improve file performance.
• Simplify data sharing and collaboration.
• Enable easier organization of data.

By leveraging VBA (Visual Basic for Applications), you can automate this process, eliminating the need for manual efforts and reducing errors.

2. Preparation Steps Before Splitting Sheets

Before using VBA to split sheets, ensure the following:

1. Create a Folder: Create a folder to store the resulting files after splitting the sheets.
2. Save the Workbook: Save the current workbook containing all sheets into the folder you created.

These preparatory steps help VBA determine the file-saving path and ensure smooth execution.

3. Splitting Sheets into Individual Excel Files

VBA Code to Split Sheets

Below is the VBA code to split all sheets in a workbook into separate Excel files:

Sub SplitSheetsToExcel()
    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 ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

How It Works:

• Set Folder Path: The code determines the folder where the current workbook is saved (FPath = Application.ActiveWorkbook.Path).
• Loop Through Sheets: Using a For Each loop, the code iterates through every sheet in the workbook.
• Copy and Save: Each sheet is copied and saved as a new Excel file in the folder.
• Close Workbook: After saving, the copied workbook is closed.

4. Splitting Sheets into PDF Files

If you want to save each sheet as a PDF instead of an Excel file, you can use the following code:

Sub SplitSheetsToPDF()
    Dim FPath As String
    FPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each ws In ThisWorkbook.Sheets
        ws.Copy
        Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & "\" & ws.Name & ".pdf"
        Application.ActiveWorkbook.Close False
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

How It Works:

The logic is similar to the Excel-splitting code, but instead of saving the sheet as .xlsx, the ExportAsFixedFormat method is used to save it as a .pdf file.

Steps to Execute:

Follow the same steps as in section 3, but paste and run the above PDF-specific code.

5. Splitting Specific Sheets by Name or Content

If you want to split only specific sheets (e.g., sheets containing certain text in their names), use the following VBA code:

Sub SplitSpecificSheets()
    Dim FPath As String
    Dim TextToFind As String
    TextToFind = "2020" ' Modify this value as needed
    FPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each ws In ThisWorkbook.Sheets
        If InStr(1, ws.Name, TextToFind, vbBinaryCompare) <> 0 Then
            ws.Copy
            Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
            Application.ActiveWorkbook.Close False
        End If
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

How It Works:

• Filter Sheets: The InStr function checks if the sheet name contains the specified text ("2020").
• Conditional Saving: Sheets matching the condition are copied and saved as separate files.

Steps to Execute:

• Modify the TextToFind variable to specify the text you want to filter (e.g., “2021”).
• Follow the same steps as in section 3 to run the macro.