excel-sheet-splitter

Small Excel VBA utility that splits a large worksheet into multiple workbooks of fixed-size row batches, auto-numbered and saved alongside the original file

Excel Sheet Splitter (VBA)

This repository contains a simple Excel VBA macro that splits a large worksheet into multiple smaller workbooks, each containing a fixed number of data rows. It is useful when you have a long list of records and need to break it into smaller files for downstream processing, sharing, or uploading to systems that have row limits.

Features

• Splits one worksheet into multiple workbooks.
• Each output file contains a configurable number of data rows (default: 500).
• Preserves the header row in every generated file.
• Names output files numerically (1.xlsx, 2.xlsx, 3.xlsx, …).
• Saves the generated files in the same folder as the original workbook.

How it works

The core macro loops over your source sheet in batches of a given size (default 500 rows), copies the header row plus that batch into a new workbook, and saves the new workbook with an incrementing file number.

Note: This macro currently assumes your data is in column A and that the header row is in row 1.

Requirements

• Microsoft Excel (desktop version with VBA support).
• Ability to run macros (enable macros in Excel’s security settings).

Setup

• Open your Excel workbook containing the data to be split.
• Press ALT + F11 to open the VBA editor.
• In the Project Explorer, right-click your workbook:
• Insert → Module.
• Paste the VBA code from SplitSheetIntoFiles into the new module.
• Adjust configuration if needed (see below).
• Save your workbook as a macro-enabled file (.xlsm).

Configuration

Inside the macro you can customize:

Source sheet name

• Set ws = ThisWorkbook.Sheets("Sheet1")

• Change "Sheet1" to the actual name of the worksheet you want to split.

Batch size (rows per output workbook)

• batchSize = 500

• Set this to any positive integer (e.g., 1000 for 1000 rows per file).

Save path

• savePath = ThisWorkbook.Path & ""

• By default, files are saved in the same folder as the original workbook.

Can hard-code a specific path, for example:

• savePath = "C:\Users\YourName\Documents\SplitFiles"

• Make sure the target folder exists.

Data range / columns

• As written, the macro only copies column A. If your data span multiple columns (e.g., A to F), update the range definitions:

• ' Header ws.Range(ws.Cells(1, 1), ws.Cells(1, 6)).Copy Destination:=newSheet.Cells(1, 1)
• ' Data rows ws.Range(ws.Cells(startRow, 1), ws.Cells(endRow, 6)).Copy Destination:=newSheet.Cells(2, 1)

• Adjust 6 to your last column number.