I take it that that since you protected the sheet, you know what cells in each
workbook contain the information you want. (and also know the name for sheet).
Now, when you get the workbooks back, are you putting them in a single folder?
or multiple folders?
is there other files there?
I guess here is the point.
If the files are in a specific folder (and/or sub folders)
you can write a macro in your "MASTER" workbook
to open each of the files and copy the data.
Here is an example.
It makes use of a couple of techniques that will keep the screen from
"flickering"
as it opens and closes each of the data files.
I executed it with a folder of 1,132 files, and it ran in about 90 seconds.
But, the files were very small (1-2 sheets).
Option Explicit
Sub Get_Data()
Dim fs, I, MasterFile, MasterSheet, DataFile, DataSheet, DataRow
'-----------------------------------
MasterFile = ActiveWorkbook.Name
MasterSheet = "Master"
DataSheet = "Data" ' Name of sheet in data documents
DataRow = 1
'-----------------------------------
Application.ScreenUpdating = False
Workbooks(MasterFile).Sheets(MasterSheet).Range("A2:Z65000").ClearContents
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\kzkhn4\My Documents\data\xtr\pmtr"
.SearchSubFolders = True
.Filename = "*.xls"
If .Execute() > 0 Then
For I = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(I)
DataFile = ActiveWorkbook.Name
If
(Workbooks(DataFile).Sheets(DataSheet).Range("K4").Value <> "") Then
DataRow = DataRow + 1
Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 1) = _
Workbooks(DataFile).Sheets(DataSheet).Range("K4").Value
Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 2) = _
Workbooks(DataFile).Sheets(DataSheet).Range("K5").Value
Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 3) = _
Workbooks(DataFile).Sheets(DataSheet).Range("K6").Value
Workbooks(MasterFile).Sheets(MasterSheet).Cells(DataRow, 4) = _
Workbooks(DataFile).Sheets(DataSheet).Range("K7").Value
End If
End If
Next sht
Workbooks(DataFile).Close SaveChanges:=False
Next I
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub