Vlookup onto another workbook

Here is my dilemma.

I have two worksheets one that has the name of clients and one that i want to copy the names to depending on the city.

For instance:

associated to each column is last name, first name and city. i have hundreds of names associated to different cities and what i would like is from worksheet1.xls to copy all the New York clients to worksheet2.xls either when i open worksheet2 or via macro what ever is easier and because last name is in on开发者_开发知识库e cell and the first name is in the other i would have to copy both.

I saw that its possible to link cells from one worksheet to another and then do a vlookup depending on the criteria. Is that the best easiest way or is there another?

One way to do this is to add a Remote Data reference in worksheet2 that queries worksheet1.
Then when you open worksheet2 you refresh the data to get latest from worksheet1

Something like

SELECT * FROM worksheet1.xls WHERE City=YourChoice

This is not a syntactically correct query, use the query editor to help you build it

If you need help, please include the version of Excel your are using.

I can offser this macro as answer, it has the following room for improvement:

• deactivate flicker application.ScreenUpdating
• LastName and FirstName as array, to adding more data and avoid many windows changes
• Using open and close to open the excel directly, instead of having previously open.

this is the code

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'
Dim City As String
Dim FirstName As String
Dim LastName As String

City = Range("B4").Value 'B4 cell where city is
Range("C4").Select 'C4 cell where name is pasted

'go to the book, shhet and firs range with names
Windows("book_with_cities.xlsx").Activate
Sheets("year2011").Select
Range("A4").Select 'where  the data start
'main copy loop
Do While ActiveCell.Value <> ""
    'if is the city I´m lookin for copy the data to my excel of results
    If ActiveCell.Value = City Then
        LastName = ActiveCell.Offset(0, 1).Value
        FirstName = ActiveCell.Offset(0, 2).Value
        Windows("book_with_results.xlsx").Activate
        Sheets("Your_city_data").Select
        ActiveCell.Value = LastName
        ActiveCell.Offset(0, 1).Value = FirstName
        ActiveCell.Offset(1, 0).Select
        Windows("book_with_cities.xlsx").Activate
        Sheets("year2011").Select
    End If
    ActiveCell.Offset(1, 0).Select
Loop
'going back to your sheet
Windows("book_with_results.xlsx").Activate
Sheets("Your_city_data").Select

End Sub

Hope this helps

Best Regards

Alen