Transfer data from one Excel file to multiple Excel files

{ 22 Comments }

By Ashish Mathur · · · Leave a comment

Assume a two column database on a sheet named Demerge.  There are duplicate values appearing in column A of this database.  One may want to transfer all records of each unique entry in column A to as many different workbooks are there are unique entries in column A.  So if there are six unique values in column A, then six workbooks should get created (with two columns) and these workbooks should get stored on a specific folder in the desktop.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • I tried saving these output files in .csv format so I edited that code as follows…

    & “.csv”, FileFormat:=xlcsv

    But its returning error as “subscript out of range”

    Can you please help me out?

    Reply

  • Hi,

    I tried it & got it in .csv format. But if I try saving(save as) those files, it is displaying “Excel Workbook” in save as type. It’s not showing “CSV”. Moreover i’m getting some cell formating like borders. I guess CSV file doesn’t shows any formating.
    Please open one of output file & check.

    Reply

    • Hi,

      I am not sure what you mean by “But if I try saving(save as) those files, it is displaying “Excel Workbook” in save as type. It’s not showing “CSV””. When I right click on the File > Properties, I see CSV file. Also, in Windows Explorer, I see file type as CSV.

      Upload the CSV file where you see formatting.

      Reply

  • Hi Ashish,
    Thanks for this, it will be a huge help to me. I was wondering if there was a way to modify it so it could handle data in 10 columns?

    Reply

      • Great! I don’t have a file, can we just use your original template? I would just like it to split based on column A like it already does, but be able to bring over the data in columns B – K also?

        Reply

  • It is giving me a Run-time error ‘9’: Subscript out of range error. I think it might be how it is saved, so I changed the path to be to my desktop, but that didn’t work either. Sorry, I’m not sure what I’m doing wrong. I have the file folder for Data Files on my desktop and the Demerge excel file saved inside it, is that correct?

    Reply

    • Hi,

      The Demerge.xlsx files has to be saved anywhere other then the Data Files folder – so save it on the Desktop. Also, open the Demerge files and press Alt+F11 and replace C:\Users\Ashish\Desktop\Data Files\ with your actual path.

      Hope this helps.

      Reply

  • Hi Ashish,
    I just tried this on my home computer and it works perfectly. There must be something blocked on my work computer so I’ll get that figured out. Thanks so much and sorry I was such a pain! This worksheet is WONDERFUL!!!! We are constantly splitting files to each of our 25 advisors so this is going to be so great!

    Reply

  • Hi Ashish,

    I have data upto column AO and changes as below but it shows only headers in output files .
    Set source_range = Sheets(“Demerge”).Range(“A1:AO” & rows_in_range)
    source_range.AutoFilter Field:=1, Criteria1:=Worksheets(“Dummy”).Range(“A” & n + 40).Value
    Could you please assist.

    Regards,
    Rajender

    Reply

  • Hi Ashish,

    Suppose there are 3 columns of data and i would like to filter/export files based on 2 columns. Can you help me in this regards.

    Reply