VBA Consolidate Multiple Workbooks In To One Single Workbook

I have a question about a macro that was posted in the 'Tips and Tricks' section.

Consolidate multiple workbooks from a folder into one master file VBA

First, I'd like to say THANK YOU to whomever wrote it, it works FANTASTIC!!

I'd like to add something to this but don't know where to begin. My excel knowledge is high but VBA skills are nonexistent.

First, I'd like to add a list of the files that have been consolidated. (I'd be placing this starting in cell K7) This will act as a check to make sure the right number have been consolidated.

Second, I'd like to be able to start the "Select Source File Folder" in a default directory so you don't always have to browse that location (all the folders will be in one directory on our server).

If this is possible I'd be extremely grateful!

Thanks,
Andrew

Andrew, welcome to ExcelFox.

The first one is rather straight forward

Just add something like

Code:

---

.InitialFileName = "C:\"

---

within the

Code:

---

With Application.FileDialog(4).... End With

---

construct

For the second one, so from K7, do you mean to add the names of the files that have been consolidated? which then leads to another question, if you start a new consolidation, all data from K7 to below will be cleared and started afresh...?

Thanks for the quick response!

Yes I'd like the list of the files that were consolidated. The file name includes the users name that created the file, the list will allow a cross check incase someone didn't submit their report. If someone didn't do it they're tracked down, asked to submit their report and then the data will be reconsolidated, this updates the data and should update the list of files used for the consolidation.

I'm on my iPhone right now, I can give full details tomorrow of what I'm doing if that'd help.

Andrew