At this point, using a macro will make this the simplest, even if you're not familiar with macros, this should be easy enough. Here's the code I wrote for you:
Option Explicit
Sub ConsolidateDataFromFileList()
'Jerry Beaucaire 6/8/2010
'From a file listing on a specific sheet
'collect all the data from those sheets into one Master sheet
Dim fileLIST As Range
Dim fNAME As Range
Dim wbData As Workbook
Dim LR As Long
If MsgBox("Create a new consolidated list?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Set fileLIST = Sheets("Files").Range("A:A").SpecialCells(xlCellTypeConstants)
With Sheets("Master")
.Range("A3:Z" & Rows.Count).Clear
For Each fNAME In fileLIST
Set wbData = Workbooks.Open(fNAME)
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).EntireRow.Copy .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
wbData.Close
Next fNAME
End With
Set fileLIST = Nothing
Application.ScreenUpdating = True
Beep
End Sub
I've installed it into the sample workbook attached. Look on the sheet FILES and you will find a path/file listing to where I tested this on my system. Edit that so it points you your files on your system. Make sure the paths are exactly correct.
You can add as many files to the column A listing you want, in as many random locations as you wish. The macro is going to go through them all in the order they are listed, if that matters.
Then switch back the the MASTER sheet and press the button, the rest is automatic. Even with a lot of filenames, this should be speedy enough.
=============
You can install it into your other workbooks by:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
The macro is installed and ready to use. Press Alt-F8 and select it from the macro list. (Or you can add it to a FORMS button on your worksheet, like I did)
Bookmarks