Data Analytics

VBA Macro to Split Single Excel file into Multiple Excel Files basis any row count

Share with:


Dear Readers, 

A strong command in MS Excel is one of must have skill for every professional but an expertise in VBA is a lifesaver. Recently, I encountered a common situation where a platform had a limitation of accepting excel files with no more than 1000 rows. This means that if you have a single file of 1,00,000 records, then you would need to split it into 100 files with 1000 records each. Splitting and creating files manually at such point will be soulless, boring and dull task! This is where VBA comes to rescue….

Here is an Excel file which can be used to split a single Excel file into multiple Excel files basis any number of split row count. All you have to do is browse the source workbook (i.e. the one with 1,00,000 rows), select sheet name and provide the custom split count (i.e. 500, 1000..).

Download Link: Excel One to Many Files Splitter_V3.0

Input Dialog box for Excel file split Macro

Splitted files are created in the same folder as the source file

Hope this tool helps you and saves your time in day to day activities. Feel free to leave a comment, Like and Share this with your friends..

Share with:


Categories: Data Analytics, MS Excel, VBA

Tagged as: , , , ,

7 replies »

    • Sorry for delayed response. I believe you are referring to having more than 1 row as header. Version 2 coming soon.

  1. Hi, I have 3gb file and file cant be open in excel as excel support data max till 2gb. As file is received in CSV format because of high data. how to add csv file type to current macro.

  2. Hi Hardik, I have just uploaded V3 at the above link and tested the macro for maximum number of rows in Excel ~>1 million. If the problem persists, please share the raw data file (after removing any sensitive data) on my email jayant.kodwani@gmail.com..

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.