Macro to copy entries from a file (if they satisfy a condition) and save in a new file

[caleb708]

Hi,

I need to copy entries from one consolidated file and split them into different files. I have attached the file containing my data.

What I need to do is copy all the entries for one country and create & save (as country name) a new excel file containing all the entries for that country. My data is all sorted by country name, so a simple 'if' condition to copy all the cells till the country name changes should work. I would also need the heading row (1).

Path to save: C:\Documents and Settings\prabhakarc\Desktop\Caleb

Some ideas that I have: I have created a blank template file with the headings with the name Country_Data, so pasting the copied data into this file and Saving As 'Country name' copied from the consolidated file might work?

Appreciate the help in advance!

Thanks,
Caleb

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[caleb708]

Thanks! I will try this out!

[caleb708]

Hi arlu1201,

This is working great for the first Country (Argentina), however I want it to repeat the same operation for all the countries in the file.

How do I edit this code to do that?

[arlu1201]

I tested it at my end and it works fine. Ensure that you put the macro in the file which contains the data.

You do not have to edit it. It will give you the output for all the countries.

[caleb708]

Thanks! I tried it again and it worked this time. Trying the macro on a larger data set now.