Macro in excel to copy specific values from a table with a specific criteria

I want to make a macro in excel that makes the following steps:

1. Search a specific String in a specific column ("D")
2. Copy some values from specific columns of that table, which have the specific string (I had searched in step 1) in the column D, to another worksheet.

So in order to do this I have:

Please Login or Register  to view this content.

Now I'm not sure how to manipulate the range variable I got from the search. How can I say for example, regarding the table bellow, that I want to copy the values from the "Country" and Population columns that corresponds to the "Lisbon" value in the city column to another worksheet ?

ptINZ.jpg

Thanks in advance

Hi Berlan,

Thanks for the tip. I've some doubts on your code though. In the last part, why do you do the Intersect stuff? I adapted the code to my files and became something like the code bellow where I want to paste the information in a another workbook begining in the Cell A15. When I run the macro it gives me a warning box saying "Run Time Error '9': Subscript out of range"

Please Login or Register  to view this content.

I had tried also replace the Itersect part for something more basic like the code bellow but it also failed giving me the same message

Please Login or Register  to view this content.

Can you help me again?

Thanks

Hi again Berlan,

I solve that problem with minor changes. End up with the final code bellow (just changed the Intersect part). Just one more question, If I want to copy another column beside the ones comprised by the A:D range i.e if I want to copy the A:D range plus the Z column how can I do the intersection?

Once again, thanks for the help =)

Please Login or Register  to view this content.