Macro to seach column and copy row

350first

New Member
Joined
Oct 12, 2006
Messages
3
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 12, 2006
Messages
3
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
Office Version
  1. 365
Platform
  1. Windows
And why can't you do that with what I suggested?
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,367
Members
410,911
Latest member
AniEx
Top