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..).
16 Nov 2023 – Version 4.1 released with much awaited enhancements such as static rows and split by less than 10 rows.