Mass consolidation of specified fields from different cell locations

[Verukins]

Hi all,
I have approx. 2000 excel files, from which I need to consolidate certain cells - which are in different cell locations for each document.

What I would like to do (if possible) is search for a specific text string, then copy the cell to the right of it into a "master" spreadsheet.

In addition there is one field which is one line below specific text in some of the documents and two lines below in other documents. (In the case where it is two lines below, the line in-between is blank if that helps?)

The cycling through all the documents seems to be the easy bit - but i'm struggling with doing a search/select one cell to right (or down or whatever) then copy.

Any help much appreciated

[Steve7913]

I`m no expert, but i`ll give this shot.

you already have the code to cycle through your work books. for the below, I will use MasterFile, and WKB1, WKB2 as WorkBook 1 and workbook 2 and so on.

You may need some modifications, but i hope this helps.

Please Login or Register  to view this content.

[Bernie Deitrick]

Within your loop, use code like

Please Login or Register  to view this content.

[Verukins]

Hi guys, thanks for the replies....

It took me a little while - and I went around the web "code borrowing" but now it sits right in my head and is working...

Thanks for the assist.

For others - this is what I ended up using.... im sure it can be done much more efficiently... but the fact is that for me, it consolidated just under 2000 spreadsheets with all the right info in under 2 mins.

Please Login or Register  to view this content.