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:
Sub MyMacro()
Dim rngSearch As Range, rngFound As Range
'Ask for the string I want to find
site_name = InputBox(prompt:="Please enter site name:")

Set rngSearch = Range("D:D")
Set rngFound = rngSearch.Find(What:=site_name, LookIn:=xlValues, LookAt:=xlPart)
If rngFound Is Nothing Then
MsgBox "Not found"
Else
.
.
.
End If
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