text search between workbooks, copy data
 

I have a workbook titled “data comparison” that contains sample i
numbers in column B ( example: Sample1, Sample10, sample32 etc.).
I would like to write code that opens a second workbook title
“results”, searches column A of that workbook for the matching sampl
ID (ex. Sample32), then copies data in the row with the matching sampl
ID and pastes the data back into the first workbook (data comparison).
Specifically, I am having trouble with the search method between tw
workbooks. Any tips would be helpful. Thank

--
Message posted from http://www.ExcelForum.com

text search between workbooks, copy data
 

When dealing with two separate workbooks, you'll need to specify the
workbook in the code. I suggest creating two Workbook variables and using
them throughout. For example:

dim CompareWB as Workbook
dim ResultsWB as Workbook
dim SampleID as string
dim rFind as Range

set CompareWB=Workbooks("data comparison.xls")
set ResultsWB = Workbooks("results.xls")

rowcount = CompareWB.Worksheets(1).UsedRange.Rows.Count
for row = 1 to rowcount
SampleID = CompareWB.Worksheets(1).Cells(row,2).Value
set rFind = ResultsWB.Worksheets(1).Columns(1).Find(What:=Samp leID,
LookAt:=xlWhole)
if not rFind is Nothing then
ResultsWB.Worksheets(1).Cells(rFind.Row,
2).Copy(CompareWB.Worksheets(1).Cells(row,3))
endif
next row

"G8834 " wrote in message
...
I have a workbook titled "data comparison" that contains sample id
numbers in column B ( example: Sample1, Sample10, sample32 etc.).
I would like to write code that opens a second workbook titled
"results", searches column A of that workbook for the matching sample
ID (ex. Sample32), then copies data in the row with the matching sample
ID and pastes the data back into the first workbook (data comparison).
Specifically, I am having trouble with the search method between two
workbooks. Any tips would be helpful. Thanks


---
Message posted from http://www.ExcelForum.com/