Macro to separate data based on contents of a column to 2 new excel files

Hello Experts,

I need one help from you guys, I tried recording macro for this but it doesn't work every time and hence i end up incurring a lot of time on this. Thanks a lot in advance. Appreciate your kind help


I have attached the initial file that i work on. So, basically i need 2 files created from this file as per below:

1. The basis of creating 2 new spreadsheets from the attached file is the column D - "Host Country/ Responsible Entity" - I need to create 2 files that should look similar as the attached but one should only have details of countries starting with "USA - " while 2nd file should have details of all non US line items.
2. I would want both the files looking as same as the attached file with 1-6 rows same for both and rest based on the above explanation.
3. The catch here is - Everytime the number of rows and columns fluctuates and that is what i have trouble with. So, if you can share the macro code that identifies the last row/column of the sheet and then inserts total at the most bottom that would be a great help.

Thanks again for all your help

Here's a possibility.

Originally Posted by beautify5

Thanks. It works like a charm but can you please help to add to the code to create new files for each tab, one with US and other Non US. File can be auto saved to the location of the original file. Hope this is not a big trouble for you. Thanks a lot again.

I'd be happy to, but where I am it is very late. I have to go to bed now.
Someone may step in and do that for you, but my guess is a new thread with a link to this thread might get more attention.

Thanks for the rep. points.