Macros to Split an Excel File into multiple files taking into account information in Rows

[fwaszuk]

Hello, I am kinda new with Excel, in my work we handle Excel files that have 20+ mb file size.
We need to send this files to our clients, hence we need to split them.
Is there any type of macro that splits the excel file into multiple one, and also considers the information in the rows?

As an example:
1 AAA 1 ABCD S 485 INFO
2 BBB 2 ABEF P 484 INFO
3 CCC 3 QWER A 123 INFO
4 DDD 4 TYUI A 345 INFO
5 EEE 5 HGYJ A 646 INFO
1 AAA 1 ABCD S 485 INFO
2 BBB 2 ABEF P 484 INFO
3 CCC 3 QWER A 123 INFO
4 DDD 4 TYUI A 345 INFO
5 EEE 5 HGYJ A 646 INFO


Lets say that this file is endless, I need Excel to split it in multiple files, using as reference two different type of information:
Creating files that are around 3000 rows
When file is splited, the next file should start with an S in the E column, that means that if in order to the next file start with an S the previous one should have 2980 that is no problem
The E column does not always follow the same pattern, but each client info starts with an S, that is what is crucial for this to succeed

[MarvinP]

Hi fwaszuk and welcome to the forum,

There is a newer tool in Excel called Power Query that you can install as an add-in that will do this work.
https://www.microsoft.com/en-us/down...4-6deafc65a865

In the newest versions of Excel Power Query has been renamed to "Get and Transform Data".
The idea is that you would create a new workbook and run this tool. You would point to your big workbook (might need to be a table) with all your data. Using Power Query you would filter the big data down to a filtered set of data. Then you would import that subset into this new workbook and send to your customer. You create many of these different Power Query filters that each creates workbooks and problem solved.

Learning Power Query is a new step for us old timer Excel users. The claim is that it is much easier to learn than VBA and now that I now something about it, agree.

Start learning Get & Transform Data (Power Query) to solve this problem.

[fwaszuk]

but with Power Query are we able to split an excel file into multiple files?