StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I can offser this macro as answer, it has the following room for improvement:</p> <ul> <li>deactivate flicker <strong>application.ScreenUpdating</strong></li> <li>LastName and FirstName as <strong>array</strong>, to adding more data and avoid many windows changes</li> <li>Using <strong>open</strong> and <strong>close</strong> to open the excel directly, instead of having previously open.</li> </ul> <p>this is the code</p> <pre><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 </code></pre> <p>Hope this helps</p> <p>Best Regards</p> <p>Alen</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload