Required different location data to different file

Hi All,

i have a excel file: Column A: Sr no, Column B: Location name (India, China, Pakistan,Singapore etc ), Column C: Property ID..etc..

Total 2000 rows with 18 locations,

Requirement: 18 different files for different different locations.

Current procedure: Doing filter and cut paste to different file.
Required procedure: Macro or something.

Please help me on this.. (File attached)

Prem

This is not a very complicated routine, it is just a lot to explain.

I have included two attachments.
1 is the workbook that you provided
2 is a folder with the named workbooks inside.
Unzip the folder and place it anywhere you choose to on your system.

Use my attachments as an example until you get this working correctly.

Open the Workbook "All Location data B"
In Cell L2 enter the folder location that your files are in, make sure of the final slash in the location.
For me the location was:
C:\Users\davesexcel\Downloads\TestFolder\

Click the Button and let the action roll.

This is the Code I used.

Please Login or Register  to view this content.

What is this code doing?

The 1st part is using advanced filter.

Please Login or Register  to view this content.

It finds all the unique items in Column B, copies and pastes it to J1. This part is required to start the loop. It is assumed that each workbook is named by this list and that they are .xlsx workbooks.

This Part...

Please Login or Register  to view this content.

This will loop through column J, filter column B using Column J as the criteria,open the workbook with the name that is in column J, copy and paste the filtered data to the first empty row in the workbook, save and close that workbook, then move on to the next item in column J.

Good luck and happy coding.

Hi Daves,

Very thankful to u on this.. this is working fine but there is a small point that in this sheet there are only 5-6 columns but actually m having 15-20 columns to copy...
is this possible??

Here is the workbook using 15 columns.
If you compare the two codes you will see the adjstments.