Group Excel Sheets & Save Them As Separate Workbooks

Introduction

In this tutorial, I will show you how to split each worksheet from multiple Excel files into separate workbooks. This can be particularly useful when managing financial data across different months. For instance, I have a folder containing several Excel files representing financial data for January through May. Each file contains various worksheets, including Sales, Profit, Networking Capital, and Headcount.

To begin, let’s take a look at the structure of our data:

• January data: Sales, Profit, Networking Capital
• February data: Sales, Profit, Networking Capital, Headcount
• March data: Sales, Profit, Networking Capital
• April data: Sales, Profit, Networking Capital
• May data: Sales, Profit, Networking Capital, Headcount

Now, to split these worksheets into individual workbooks, I’ve created a Python script using the xlwings library. This script will help automate the process. Let’s dive into the steps involved:

Step 1: Install Required Libraries

First, you need to install the xlwings library. Open your command prompt or terminal and run:

pip install xlwings

Step 2: Set Up the Script

Next, create a new Python file and import the necessary libraries:

from pathlib import Path
import xlwings as xw

Now, let’s define the source and output directories. If your current Python script is in the same directory as your Excel files, you only need to specify the folder name. Otherwise, you can enter the full path to the folder.

source_dir = "Month_End_Data"
output_dir = "Output"

Step 3: Iterate Over Excel Files

We need to iterate over all Excel files in the specified source directory. For each worksheet within those files, we will create a new workbook:

excel_files = list(Path(source_dir).glob("*.xlsx"))

Next, let’s create a dictionary to store the names and paths of the Excel files in the output folder:

excel_outputs = {}

Step 4: Copy Worksheets to New Workbooks

For each Excel file, open it and iterate through its worksheets:

for excel_file in excel_files:
    wb = xw.Book(excel_file)
    for sheet in wb.sheets:

For each worksheet, create a new workbook and copy the worksheet into it:

Step 5: Save the New Workbooks

After copying the worksheet, set the name of the new workbook and save it:

Step 6: Handling Existing Workbooks

To avoid overwriting existing workbooks, check if a workbook with the same name already exists in the output folder before creating a new one. If it does, append the worksheet to the existing workbook instead:

Step 7: Run the Script

Run the script to split the worksheets into separate workbooks. Once completed, you should see the individual workbooks in your output folder, each containing the respective data:

Conclusion

This tutorial has walked you through the process of splitting Excel worksheets into separate workbooks using Python. By automating this process, you can save time and effectively manage your financial data. Feel free to modify the script to suit your needs, and if you have any questions, don’t hesitate to ask!

Further Links

• GitHub Repository for the Script