Macro to seach column and copy row

[350first]

I have a few hundred excel files with 'raw data'; each excel file has a unique filename.
I then have 1 'master' excel file that has all of the 'raw data' file names in Column A, and other data for each 'raw data' file in Column B, C, and D (these 3 columns are the physical location of where the 'raw data' from taken from...X,Y,Z cordinates).

I'd like to copy the matching row from the 'master' file and insert it into Row 1 of the corresponding 'raw data' file.


My plan is to:
-open ~100 'raw data' files and the 'master' file
-I can copy the filename of the 'ActiveSheet' and then bring the 'master' file to the front.
-**I then need to perform a search to match the 'filename' I copied, to Column A of the 'master' file, and then copy the row which matches the filename.**
-after that, I can just insert the copied row back into the 'raw data' file, save, close window, and loop the macro to do this function for all 99 'raw data' files remaining.

**I don't know how to write the search portion...any help?


thanks,
Peter

 

[Norie]

Peter

Do you actually need to search?

You said the names of the files are in column A of the master?

So why not just loop through those filenames, open the appropriate file, do the copying and pasting then close the file.

 

[350first]

But I only want to copy the 1 row of the 'master' file that corresponds to the 'raw data' file, to the 'raw data' file.


Master excel file:

____A___B___C___D
1__001__10__10__10
2__002__10__10__20
3__003__10__20__30
4__004__20__30__40


raw data files names: 001.xls, 002.xls, 003.xls, 004.xls


for file "001.xls", I want to insert ROW 1 from the Master File to ROW of "001.xls


thanks

 

[Norie]

And why can't you do that with what I suggested?

Dim wb As Workbook
Dim rng As Range

Set rng = Range("A1")

While rng.Value<>""
     Set wb = Workbooks.Open("C:\" & rng.Value & ".xls"
     rng.Offset(,1).Resize(,2) wb.ActiveSheet.Range("A1") 
     wb.Close True
     Set rng = rng.Offset(1)
Wend