How to Split Each Worksheet in an Excel Workbook into Separate Files Using VBA

Managing large Excel files with multiple sheets? 😩 Want to separate each worksheet into its own Excel file — with all formulas and formatting intact?

This step-by-step guide will help you do exactly that using a simple VBA macro! 💻

✅ Why You Might Need This

• You have a workbook with 25+ sheets and want to save them individually.
• You want to preserve formulas, formatting, and structure.
• You want an easy way to select the destination folder for the split files.

🛠️ Example Used in This Blog

In this example, we created a sample workbook called Sales_Report_2025.xlsm with 3 sheets:

• North
• South
• West

Each sheet contains:

• Product sales data 🛍️
• Formulas (like =Quantity * Price) to auto-calculate totals
• Proper formatting (bold headers, colored cells, ₹ currency format)

📌 Screenshot: Sample data in “North” sheet 👉

📌 Screenshot: Sample data in “South” sheet 👉

📌 Screenshot: Sample data in “West” sheet 👉

📜 VBA Macro to Split Worksheets

Here’s the VBA code to copy-paste into your Excel:

🧩 What It Does:

• Prompts you to select a destination folder
• Saves each sheet as its own .xlsx file
• Preserves formulas, formatting, and layout

📋 Steps to Run the Macro:

1. Press Alt + F11 to open the VBA Editor.
2. Insert → Module
3. Paste the following code:
   • Sub ExportSheetsToFiles()
Dim ws As Worksheet
Dim FolderPath As String
Dim NewWorkbook As Workbook
FolderPath = "C:\Users\PREMIUM\Desktop\Excel Data\Extracted\" ' Change the path to your desired folder
On Error GoTo ErrorHandler ' Enable error handling
For Each ws In ThisWorkbook.Sheets
ws.Copy ' Create a new workbook with the copied sheet
Set NewWorkbook = ActiveWorkbook ' Reference the new workbook ' Save the new workbook NewWorkbook.SaveAs FolderPath & ws.Name & ".xlsx" NewWorkbook.Close SaveChanges:=False ' Close the new workbook without saving changes again Application.Wait (Now + TimeValue("0:00:02")) ' Wait for 2 seconds to prevent Excel from freezing
Next ws
MsgBox "All sheets have been saved successfully!", vbInformation ' Confirmation message
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical ' Show an error message if something goes wrong
End Sub

📌 Final Output Example

After running the macro:

• You’ll have 3 separate files:
  • North.xlsx
  • South.xlsx
  • West.xlsx
• Each file will contain the original formatting, formulas, and values 🎯

📥 Try it out and share your feedback in the comments!

Download the Sample file : Sales_Report_2025.xlsm