Macro to compile data from multiple excel files into one summary file

[Kiwirunner]

Hi,

I have one hundred excel files that have the same identical format but have different guest names, guest addresses and arrival dates. What I am trying to do is write a macro that goes into each file, looks to see how many guest names are in each file and then copies this information along with the guests address information into a summary excel sheet.

The 100 identical excel files look as follows (each file will be saved as the group name i.e. "Group 12"):

A B C D E
1 Group 12
2
3 Guest Name Address Arrival Date
4 Joe Bloggs Australia 21/1/12
5 James Henry UK 22/1/12
6 Sarah Henry UK 22/1/12

I am trying to get the summary file to look as follows:

A B C D E
1 Summary File
2
3 Group Guest Name Address Arrival Date
4 12 Joe Bloggs Australia 21/1/12
5 12 James Henry UK 22/1/12
6 12 Sarah Henry UK 22/1/12
7 13 Andrew Walker UK 28/2/12
8 13 Kate Henly USA 29/2/12
9 14 Andy Eaden A 29/2/12

Any help in pointing me in the right direction here would be most appreciated.

Thanks and have a happy NYE!

 

[laxmananm]

Dear Jerry,
First, I would like to thank you for these great postings.
I have been looking at the solutions you have provided and tried to adjust them to my specific scenario with no luck. I must be doing something wrong and would appreciate if you could provide your guidance. I have more than 100 workbooks contained in some file folders . I would like to extract the data in specific cells from those workbooks and copy them into a summary workbook.
The path name is C:\Jose\AMO\Coater 1 & 2 and the files are named as L11,L12,L13,L41,etc. Inside these file is where my workbooks are located . In example, in file L11, I have workbook "L11F3R GEN M.3 13.00" from which would like to pull the data in worksheet named "QC spec" and cells A1 (Item description),D1 (Item No.),B14 (Specs T) and B15 (Specs R) . (The same cells and worksheets in all the workbooks).
These information would be copied into a Summary workbook and worksheet1. It would look like this

A B C D
1 Item No.(Source: cell D1) Item Description (source: cell A1) Specifications T (source cell B14) Specifications R (source cell B15)
2
3
4
5
etc,

Again, your guidance is tremendously appreciated.
Jose

Hi Jerry,

Please find my requirements on the below to create tool..

Hi Sunil,


I need one help from you.I am trying to create one macro tool for consolidation of reports.After a long surf i found your address to ask help.If you wish Please reply to this mail.
i will send you the needs and sample sheets for reference.


I have the below sheets..


1.Consolidated_Master sheet.xlsx
2.Month Sheet_X1
3.Month Sheet_X2


like wise i have 30+ sheets in my folder.But i need to maintain only consolidated sheet for my reports.




My requirement:


In Month wise sheet having datas and i need to copy and paste the particular row dats into the specific consolidated sheet
row.i have 7 tabs in my consolidated sheet.But in the month data sheet i have 4 tabs.But all the required column datas are
present in the month wise sheet for various projects(like X1,X2..X30+..).


Main thing here is by using column name (As a reference) i need to collect and paste the into a conslidated data sheet(By
a single click using macro).Rest of the cells having formulas which will calculate the report automatically.




Thanks,
Lakshmanan M

 

[seekon]

Hi All,

This code is perfect for my use, however I would like to be able to place the consolidated data directly inside a formatted table, to allow the formatting and the auto formula calculations to automatically calculate as data is imported into the master sheet.

Currently the data is copied outside of the table that I set up, see below.

<img src="http://sekhon.ca/excel/Line2MM.png" width="529" height="305" />

Thank you kindly

 

[ram8gig]

hi,
i have most likely the same issue.I have 50 excel files that have the same identical format but with different file names located in the same folder. and needs to consolidate the total number of hours/minutes in CELL G27 to a an existing file (consolidated tracker). thank you!

 

[pidnani]

Hi Jerry,

Your code is excellent, however if I need to combine into 1 file only 1 specific tab from several files that are located in different folders, do i first copy all the files into 1 folder then use the code?

Many Thanks for your advise in advance.

 

[jbeaucaire]

Currently most of the consolidation macros I write expect all the source files to be in the same folder, so yes, once they are all in one central repository folder, fire away.

 

[pidnani]

Hi Jerry,

I have now put all the files in the same folder, so I have code below given to me by VBgeek which work brilliantly however it copies link and I want it to copy as values, also if I need to add another tab to combine how would i tweak that?

I also get messages that say the detination worksheet contains the same data, to rename it or change it copy, I am trying to eliminate that message as well and just copy as values no matter if it has the same name.

Sub CombineAll()
Const sheetName As String = "GB00"
Const Folder As String = "C:\" ' your folder, make sure ends with \

Dim fileName As String
Dim wks As Worksheet
Dim wkb1 As Workbook
Dim wkb2 As Workbook

fileName = Dir$(Folder & "*.xls*", vbNormal)

Set wkb1 = Workbooks.Add

Do Until fileName = ""
Set wkb2 = Workbooks.Open(Folder & fileName, , True)

On Error Resume Next
Set wks = wkb2.Sheets(sheetName)
On Error GoTo 0

If Not wks Is Nothing Then _
wks.Copy , wkb1.Sheets(wkb1.Sheets.Count)

wkb2.Close False
fileName = Dir$
Set wks = Nothing
Loop

End Sub

Thanks

 

[joshuaa]

Hi Jerry, i appreciate any help you can provide. i have multiple excel files in the same folder and i need to be able to input a range (in this case dates/time ie 15/7/2015 12:07:13 AM to 16/7/2015 12:06:13 AM dates and time are in different columns) i need to search multiple files for all entries within that date and time and copy the entire row to a new spreadsheet.
If thats possible i apprecate any help

Thanks

 

[jbeaucaire]

Don't do that. Use one of the many examples in this thread to pull ALL the data from all the various source files into one single database sheet. Do this one time and you're done needing fancy macros.

Once the data is consolidated into a single sheet, you can use Excel's built in Data > Filters to show only certain rows and hide the others, all right there in the main database. Once you've filtered by a specific date/time range, for instance, you can easily copy those visible rows off to some report or such. I typically try to just deal with the data right there in the main database.

 

[bmrr2]

Hi Jerry,

I am hoping to be able to use your code, but unfortunately, the antiquated system that we’re using outputs all of the workbooks that we need to consolidate the data from into e-mails. The e-mails are pasted into a folder on a shared drive, but I do not know of a way to easily/automatically open each excel file within the outlook items for the macro to view the content of the workbooks.

Do you know of any way around that?

It seems like the only way to do this that I’m aware of is to save each workbook (35+ of them) to a new folder before running the macro, but that seems counterintuitive time-wise. Any input would be greatly appreciated

Thanks,
Amber

 

[jbeaucaire]

I don't use Outlook, so no first-hand experience doing that, but on first glance it sounds no more complex than any other macro, just need a primer on that topic:

Google: Outlook vba save attachments

I see Ron de Bruin in those results, always good reading. Anyway, once you have a macro setup to save off your attachments for you, then the other macro is usable.