Split Data into Multiple Workbook based on Unique List

Hi all,

Please help to create a macro to split the data into multiple workbook. Created file should be save in one folder and create a specific filename based on unique namelist and date. I have attached the sample data and the template format for the extracted data.

The macro would be able to create a unique list for Buyer and Seller column. From the unique list, extract the data and use the template format.

I hope you can help me with this as I do it manually by copy and paste.

Hi !

You can copy the buyers and the sellers in a helper column then use the Excel feature remove duplicates from Data tab …

Then use an advanced filter in order to filter and copy - a single codeline ! - to another sheet …

hi Marc,

I was able to do that in excel formulas. I have attached the file that I'm using for a clearer picture. In the attached "Recap_Data" file, I paste the data in Test sheet. Then, on the trade recap sheet, I key in the client name, as an example "Ayala" in cell C5. Then all data will be populated in A:1 then link into K:P column for copy and paste to new template workbook as attached on my first message.

All I want is to eliminate the manual copy and paste and creating of the individual file per client. I believe that this can be done in VBA but don't know how.

With an advanced filter no need the trade recap sheet.

But we need the template worksheet, whatever in the source data workbook or in a standalone workbook
and all the details you forgot to explain in the initial post as we are not mind readers …

Apologies if I fall short in explaining in my initial post.

I have attached the template workbook or the sample workbook that should be created and save in the folder when the data was split. The filename of the created workbook should be the client name and the trade date.

thank you.

This is not a template but again a final expected result workbook !

So just read again - well this time ! - post #4 and your initial post in order to 'fill the holes'
or you will must modify yourself the code …

Attached is the source workbook "Recap_Data" and the sample expected result workbook "Ayala_13Sep2019"

Detailed below is the process on how I manually create the individual workbook:

1. From the source workbook, data is pasted in "test" sheet,
2. In trade_recap sheet, a unique list was created in column AA.
3. In Cell C5, I manually input the client listed in the unique list
4. Then the data populates in A:I columns, necessary columns was linked in K:Q columns
5. Copy the found data starting from K11:P11 columns
6. Paste the data to A7 of expected result workbook.
7. Save the expected workbook with filename as "clientname_date"
8. Next client and Repeat the process 3-7

I'm asking help if this process can be done in VBA.
Hope this clarifies.

thank you.

Originally Posted by Malou

6. Paste the data to A7 of expected result workbook.

Should we guess from where ?!

Expected result workbook should be the sample manually created file "AYALA_13Sep2019"

No as yet explain in post #4 instead of a totally useless Trade Recap sheet (for any VBA procedure)
you must create a template 'Summary' sheet so empty (without B2 cell filled neither headers & data)
just with the first two rows yet formatted as the easy way is to copy a template worksheet to create each new workbook …

So from your initial post, open the Recap.xlsx workbook, create the Summary worksheet with just the first two rows well formatted,
save this workbook to the .xlsb binary format then attach it here.
Or the Summary empty worksheet can also be saved as a standalone Summary.xlst template model workbook,
whatever but we need it !

You need too to read your initial post again in order to 'fill the holes' to explain what we can't guess
or you will have to modify the code (as a one shot code, I won't modify it later if you forgot anything) …