Thread: Copying specific rows of data to another workbook

I'm looking to create a macro that, when run from my current workbook (Book2):

• Opens another workbook (Book1) in the background.
• Finds the rows that correspond with a specific word (in this case 'Germany') down column B of Book1.
• Copies row of data corresponding to the specific word, in this case between columns A:F
• Pastes this row of data into my current workbook (Book2) on the next available line. *The row of data it copies should not be duplicated in my current workbook. i.e. it should simply add the updated information entered into Book1 to the next line of Book2
• Automatically save my current workbook once updated.

I've had a go, but my solutions are longwinded and dodgy. I was hoping that someone could show me how to do this efficiently..

Examples of Book1 and Book2 are attached

hi, as far as I understand the second workbook is the result to obtain. Why are there only two rows with "Germany" when the total number of them is four in original data.

Hey watersev. Yep you are right, the second workbook is the result to obtain and yes, the final result in this example should have four rows of "Germany" in it, not just two. I just uploaded the example data I was using to see if my attempts were working, and also to ensure my aim was easy enough to understand.

can you also clarify about duplicates, it would be nice to have an example of this event

If Workbook1 only contained the following data:
1 Germany a a a a
2 Italy b b b b
3 Spain c c c c
4 France d d d d
5 Germany e e e e

Then Workbook2 should look like if the macro was run once:
1 1 Germany a a a a
2 5 Germany e e e e

If you were to run the macro a second time, a duplicate would look like:
1 1 Germany a a a a
2 5 Germany e e e e
3 1 Germany a a a a
4 5 Germany e e e e

This is what I don't want to happen.

Everytime I run the macro in Workbook2, I just want the data pulled from Workbook1 to be new or unique in columns B:G of Workbook2. So if you ran the macro a second time the result in Workbook2 would simply look like:
1 1 Germany a a a a
2 5 Germany e e e e

save both files to the same folder, open "Book1.xls" and run macro "Germany". Second file can be either open or closed.

Thanks watersev! It works perfectly..
Just one thing - it seems that the new data doesn't appear in Book2 when you run the macro while Book2 is closed.Will adding a SaveAs command for Book2 in the macro solve this?

that's right I did not put save command for Book2.xls, as to my testing it worked correctly for both cases though Book2 changes has not been saved