Want to merge specific cells from 1000+ files into one master file

**dunnybocter** · 02-14-2013, 02:07 PM

Hello, thank you in advance for any help you can provide. I have 1063 files with the same format, all are titled [somethinghere]_Expression_Report.txt and they are all in the same folder. The contents of the files have four columns and I want to copy specific contents into a master file (ExpressionCounts19.xlsx).

From each file I need to retrieve the following cells and paste them into a single new column in the master file:

B2
B4
B5
C10:C2334

Here is an example file 801120_Expression_Report.txt

Then I need to open the next file and do the same thing for the next column of the master file and so forth. The text in the cells I need to paste is all numbers. Is this possible with a macro or formula?

Thank you!

**JBeaucaire** · 02-14-2013, 03:16 PM

this should do it.... install in your master workbook, name the main sheet Master and edit the fPath to where your text files are stored.

Please Login or Register  to view this content.

**mrice** · 02-14-2013, 03:27 PM

Ah, the joys of next generation sequencing

Try the following changing the path as appropriate.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**dunnybocter** · 02-14-2013, 05:39 PM

Yes yes, NGS :-)

I appreciate both replies but something is hanging up when I try either script, so I assume I am setting up the macro wrong. When I run either of them absolutely nothing happens, but when I run other macros I've written or obtained from nice people like you they work fine. I'm trying to solve the issue and thank you for a place to start.