3 Ways to Save Excel Sheets as Separate Files

In the vast landscape of spreadsheet software, Microsoft Excel reigns supreme. One common task users often face is the need to split an Excel workbook into multiple individual files, each containing only a single sheet from the original document. This can be particularly useful for data organization, privacy, or simply for ease of sharing specific parts of a workbook. In this article, we will explore three methods to save Excel sheets as separate files, ensuring you can efficiently manage your Excel data.

Method 1: Using Excel's Built-in Features

Excel itself provides a straightforward way to save individual sheets as separate files without requiring additional software or complex scripts:

• Open your Excel workbook: Start with the workbook that contains multiple sheets you want to save as individual files.
• Select the sheet: Click on the tab of the sheet you wish to save separately.
• Copy the sheet: Right-click on the sheet tab, select Move or Copy, and in the dialog box that opens:
  • Check the box Create a copy.
  • From the dropdown, choose (new book).
  • Click OK. This action will open a new workbook with the selected sheet.
• Save the new workbook: Go to File > Save As or press CTRL + S, choose the location, file name, and click Save.
• Repeat the process: You can follow these steps for each sheet you want to save as a separate file.

❗ Note: This method does not automatically number or rename files sequentially.

Method 2: Using VBA (Visual Basic for Applications)

Automating the process with Visual Basic for Applications (VBA) can be highly efficient if you regularly need to split workbooks:

• Open VBA Editor: Press ALT + F11 to open the VBA editor or go to Developer > Visual Basic.
• Create a new module: In the VBA Project window, right-click on any of the objects under Microsoft Excel Objects, choose Insert > Module.
• Enter the VBA code: Use the following script:

  
  Sub SaveEachSheet()
      Dim ws As Worksheet
      Dim folderPath As String
  
      folderPath = "C:\Path\To\Folder\"  'Specify your desired save location
  
      For Each ws In ThisWorkbook.Worksheets
          ws.Copy
          ActiveWorkbook.SaveAs Filename:=folderPath & ws.Name & ".xlsx"
          ActiveWorkbook.Close False
      Next ws
  End Sub
  

• Customize folder path: Update the folderPath variable with the directory where you want to save the files.
• Run the macro: Press F5 to run the script.

VBA offers a faster way to save sheets as separate files, especially when dealing with multiple sheets or when you need to perform this task frequently.

📌 Note: Ensure you've allowed macro content to run VBA scripts.

Method 3: Using External Tools or Add-Ins

If you prefer a more user-friendly approach without delving into VBA or are dealing with Excel files that contain a large number of sheets, third-party tools can be invaluable:

• Download and Install: Choose a tool or add-in like "Ablebits Ultimate Suite for Excel" or "XLSplitter" and install it.
• Use the Add-In:
  • Open the workbook in Excel.
  • Go to the Add-Ins tab or where the new tool has placed its command.
  • Select an option like Workbook Tools > Split Workbook.
  • Follow the on-screen instructions to split the workbook into multiple files.
• Save the Split Files: Specify the output directory, and the tool will do the rest.

External tools often provide more options like naming conventions, saving formats, and even the ability to split based on criteria other than sheets.

Now that we've covered the methods, let's review the benefits of each:

Each method has its place depending on your familiarity with Excel, the frequency of performing this task, and your comfort level with software installation or scripting.

In summarizing this guide, we have explored three distinct ways to save Excel sheets as separate files, catering to different needs and user proficiencies. Whether you opt for the simplicity of Excel's built-in features, the automation power of VBA, or the ease of use of external tools, the choice depends on how you balance convenience, automation, and control. These techniques not only help in managing large datasets but also in improving data security by limiting access to sensitive information. Keep these methods in mind for your next project or when you need to distribute parts of your Excel data efficiently.

Can I automatically save each sheet with a sequential number?

+

Yes, with VBA or external tools, you can customize the naming of files to include a sequential number.

Do these methods work with all versions of Excel?

+

Generally, yes. However, VBA functionality might differ slightly between Excel versions, and certain add-ins might not be compatible with older versions of Excel.

What if I only want to split certain sheets?

+

You can modify the VBA script to exclude specific sheets by name or criteria, or use external tools with options to select sheets for splitting.

Can these methods save files in different formats?

+

Yes, both VBA and external tools can save files in various formats like .xls, .csv, .pdf, etc., depending on your needs.

Is there a risk of losing data when using these methods?

+

If you follow the methods correctly, there shouldn’t be any data loss. However, always backup your original workbook before making changes or running scripts.