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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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
76,351
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top