Merging Data from Worksheet from Multiple Workbooks

Hi guys,

Im new to the Macro part of excel. Im trying to figure a way out to make a serial number summary worksheet from 3000 plus files/workbooks. I found RDB merge add-in, it is close to what im looking for but dont want it to add blank lines (no data entered) and to also add invoice number and date from worksheet as well for every item on that report? So i can have a inspection data sheet for every serial number and corresponding data (range) in one file as a serial number history. If this makes sense?

Trent

You need to add a bit more information and uploading a sample file is a good way to start.

You should also say what information you wish to extract and how you wish it organized. Are all the files of the same layout? Does each file contain several sheets or do you only wish to extract information from one sheet?

to make a serial number summary worksheet from 3000 plus files/workbooks

Are you talking about creating a "target" file with 3000 sheets in or?

Alf

k thanx will try and make a sample sheet. Im new to this forum as of today lol. Yes all the files are the same layout and some files have more then one sheet (with the same layout and name jsut (2), (3), etc) to extract data from. Creating one file. But gotta extract data from all excel files in folder and all subfolders in a certain range and create a single worksheet with all data.

Going to play around with RDB Merge, website and see if i cant get the basic of the merge to work then try and fine tune it.

Trent

Would like to merge all files with this worksheet "Inspection Report" in it the Range B12:W31 would be what needs to be merged into one sheet from multiple files.

Perhaps this could be a starting point?

Change folder to suit your needs but this macro will not loop through subfolders.

When pasting to target workbook the "PasteSpecial" command is set to paste all i.e. formats, values and formulas.

If pasting formulas is a problem replace "Paste:=xlPasteAll" with "Paste:=xlPasteValues"
and add an extra line in the "PasteSpecial" command to paste formats if you wish to include this as well.

Please Login or Register  to view this content.

Alf

Played around with this and got it working but need to add subfolders. But it takes a long time cause it opens the file and then compies data (ADO) is supposed to be faster will pull from closed files? Not sure how to do that. And also how do you enter code so if row in that range has no data entered it will not copy a blank spot into summary sheet.