# Solved: Excel Macro - Search List of Values, Copy, and Paste



## UMCaneSimon (Oct 16, 2009)

Hello Everyone, 

First time user of the forum here and it does seem everyone is very helpful! I did a search and could not find search macro for a list of values so I'm adding a new post. My apologies if this was answered before but hopefully someone can point me in the right direction.

Here's what I have: 

I have 3 worksheets:
1. List of Search Criteria (List of 100 or so countries in column A)
2. Huge Data Set (Sales Data; column K, L, or M will contain country name)
3. Blank Output sheet

My goal is create a search button that will look in the Huge Data Set for sales transactions occuring in the list of countries specified on Sheet #1. The country info could be in any of the 3 rows (K, L, M) on Sheet #2. I would then like any row on the Huge Data Set with a matching country to be copied to the 3rd worksheet. 

I hope my explanation makes sense. 

Any help?

Simon


----------



## Rollin_Again (Sep 4, 2003)

Simon,

Welcome to the boards. It would be very helpful if you could post a sample workbook with any sensitive data removed.

Regards,
Rollin


----------



## UMCaneSimon (Oct 16, 2009)

Rollin,

Thanks for the welcome and quick reply. I've attached a sample sheet of what I want to accomplish. Here's what I want the macro to do: 

The "Countries" tab has a list of 5 search criteria (USA, CAN, CHN, VEN, BRZ). I want to find all rows in the "Data Set" tab from these countries. Note that the list of search contries can expand beyond the initial 5. 

Under the "Data Set" tab, there are 3 columns where the country information can be (Columns K, L, and M). As you can see, the 1st 5 customers are from one of the 5 countries specified. 

I want to copy the 5 customers to the "Output Sheet" tab. 

I appreciate the help!


----------



## UMCaneSimon (Oct 16, 2009)

Just giving this a little bump.


----------



## Rollin_Again (Sep 4, 2003)

I'll have something for you shortly. Was working on it earlier and my computer crashed and I just got done re-imaging it.

Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

Try this. You just need to select a country on the first sheet and run the macro. You could of course embed a button onto the sheet and then fire the code with a button click instead but we need to know if the code does what you want first.


```
Sub CopyRows()

If ActiveSheet.Name <> "Countries" Or ActiveCell.Column <> 1 Then
MsgBox ("Please Select Country")
Sheets("Countries").Select
End
End If

Set vFound = Sheets("Data Set").Columns("K:M").Cells.Find(What:=ActiveCell.Value, _
MatchCase:=False)

If Not vFound Is Nothing Then
vStart = vFound.Address

Do
Sheets("Data Set").Range("A" & vFound.Row & ":" & "M" & vFound.Row).Copy Destination:= _
Sheets("Output Sheet").Range("A" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1 _
& ":" & "M" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1)

Set vFound = Sheets("Data Set").Columns("K:M").Cells.FindNext(vFound)
Loop Until vFound.Address = vStart
Else
MsgBox ("Not Found")
End If

End Sub
```
Regards,
Rollin


----------



## UMCaneSimon (Oct 16, 2009)

Rollin,

Thanks for your help. I should've been more specify in my initial posting but I was having trouble developing a search with multiple "OR" criteria. In the "Countries" sheet there are 5 countries (and possibly more). I want to find rows from any of those 5 countries. 

The current code retricts this to the one selected country. Perhaps I need to add a "For" loop? 

Thanks again for the help.

Simon


----------



## Rollin_Again (Sep 4, 2003)

Try this instead


```
Sub CopyRows()

For i = 2 To Sheets("Countries").Cells(Rows.Count, "A").End(xlUp).Row

Set vFound = Sheets("Data Set").Columns("K:M").Cells.Find(What:=Sheets("Countries").Range("A" & i).Value, _
MatchCase:=False)

If Not vFound Is Nothing Then
vStart = vFound.Address
vMatch = True

Do
Sheets("Data Set").Range("A" & vFound.Row & ":" & "M" & vFound.Row).Copy Destination:= _
Sheets("Output Sheet").Range("A" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1 _
& ":" & "M" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1)

Set vFound = Sheets("Data Set").Columns("K:M").Cells.FindNext(vFound)
Loop Until vFound.Address = vStart
End If

Next i

If vMatch <> True Then
MsgBox ("NO MATCHES FOUND")
End If

End Sub
```
Regards,
Rollin


----------



## UMCaneSimon (Oct 16, 2009)

Rollin, 

The code works great. I had to debug and step through each line in order to further tailor it but your logic was easy to follow. Thank you so much for your help. 

Hopefully I will be able to contribute to some posts and other's questions soon! 

Simon


----------



## UMCaneSimon (Oct 16, 2009)

Rollin, 

I'm trying to enhance the search by highlight the field that matched. We are searching on 3 columns, I would like the cell that contained the match to be highlighted after it is copied to the output sheet. 

To avoid running a search on the output sheet, I'm trying to highlight the initial data sheet and copy over but I'm running into some trouble. 

Any input? 

Thanks!

Simon


----------



## UMCaneSimon (Oct 16, 2009)

UPDATE:


Rollin_Again said:


> ```
> Do
> Sheets("Data Set").Range("A" & vFound.Row & ":" & "M" & vFound.Row).Copy Destination:= _
> Sheets("Output Sheet").Range("A" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1 _
> ...


I added the following piece within the "Do" loop:

vfound.Interior.ColorIndex = 6
vfound.Font.Bold = True

It seems to work, however, I did couple a couple of match phrases that were missed when duplicates occur. For example: the search term "USA" appears in both columns K & M. Only the cell containing "USA" in column K will be highlighted and not the cell in M.

Perhaps the search ends once a match has been found?

Simon


----------



## dataminer (Dec 2, 2009)

Hi,
This is exactly what I was looking for except it keeps pasting over the data that it sends to the Output Sheet. Can anyone post the full code for this macro that wouldn't have this problem?
Thanks,
Hannah


----------

