how to find and copy values on sheet 2, based on a list on sheet 1

[Guest]

On Sheet 2 I have a matrix of 1600 rows by 5 columns (however this will grow
in rows and columns). In column C the values are unique (actually Company
symbols).
On sheet 1, I want to enter a number of company symbols (ranging from one to
many)
And then I want to use a 'macro' (or equivalent) to:
- look at the list on Sheet 1
- go to Sheet 2
- Find the same values
- Highlight the entire row(s) and /or copy them
- Paste them to a third sheet
I do not know VBA very well, so any suggestions are very much aprreciated

I am using Excel 2003 (on my PC) and then migrating the result to Office
2004 for Mac.

 

[Tom Ogilvy]

This can be done with the Advanced filter.

Sub CopyCompanyData()
Dim rng as Range, rng1 as Range
set rng = worksheets("Sheet2").Range("A1").CurrentRegion
With Worksheets("Sheet1")
set rng1 = .Range(.Cells(1,"D"),.Cells(rows.count,"D").End(xlup))
End With
rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rng1, _
CopyToRange:=worksheets("sheet3").Range("A1"), Unique:=False
End Sub

the data on Sheet2 should be set up like a database with headers in row1.

the list on sheet1 should have a header in the first cell that matches the
header in column C of sheet1

 

[Guest]

Tom

Thanks for your answer. However I find that every company code on Sheet 2 is
copied to sheet 3. And even if sheet 1 is blank, all the codes on sheet2 will
be copied to sheet 3.

Any other thoughts you might would be appreciated, thanks

 

[Tom Ogilvy]

My data on Sheet 2 (starting in A1) looked like this
ColA ColB Col C Col D Col E
Header1 Header2 Header3 Header4 Header5
312 749 AAJ 749 369
855 146 AAW 146 763
195 472 ABG 472 597
532 643 ACI 643 129
561 878 ACJ 878 685
624 686 ACQ 686 364
717 741 ACR 741 774
615 533 ADD 533 778
28 156 AGG 156 314
44 943 AGN 943 908
947 240 AGU 240 46
430 61 AHQ 61 444

continues on for over 1700 rows.

In D1 and below on Sheet1 I had my list to extract

Header3
ADD
AJJ
ANJ
AYT
BBP
BDJ
CGH
CJB
CKA
ZYP
ZZB
ZZX

Notice how the entry in D1 matches the header in C1 of sheet2
Also, there was no other data in column D of Sheet2 (macro could be adjusted
to account for the situation if this is not true).

In Sheet3, I had an entirely blank sheet.

The code as posted worked fine and pulled the 13 records from sheet2 to
Sheet3

Maybe you need to try again.

I also went back and randomly sorted both the data and the list of IDs to
extract and it still ran fine (instantly and successfully)

 

[Guest]

Tom

Finally got back to it, I've been travelling.
Thanks for the example. It was what I needed. And your VBA works a treat.

So many thanks for your assistance

Evan