Hi Steve,
The following code was an answer to another poster to this same newsgroup
about 2 weeks ago with more or less the same problem. It displays an Open
dialog box so you can select all of the files you want to process. It then
successively opens each workbook. In the "ProcessAllFiles" routine
immediately below where the comment says "'Call your macro here.", insert a
call to a routine that will actually copy the data from each worksheet to
your single worksheet. (I don't have code to actually copy each worksheet,
as I don't know whether your data has headers in row 1, whether some rows
should be ignored, etc.)
Hopefully, this will give you a start.
'----------------------------------------------------------------------
Public Sub ProcessAllFiles()
Dim varFileList As Variant
Dim lngFileCount As Long
Dim ilngFileNumber As Long
Dim strFileName As String
'Index for varFileList starts at 1 in this situation,
'even without Option Base 1 statement at the top of the module.
varFileList = Application _
.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls",
_
Title:="Open Excel File(s)", _
MultiSelect:=True)
lngFileCount = FileCount(varFileList)
If lngFileCount = 0 Then GoTo ExitSub
'User canceled out of dialog box.
For ilngFileNumber = 1 To lngFileCount
Workbooks.Open Filename:=CurrentFileName(varFileList, ilngFileNumber)
'Call your macro here.
'Set SaveChanges according to whether your macro already saved or not.
ActiveWorkbook.Close SaveChanges:=False
Next ilngFileNumber
ExitSub:
End Sub
'----------------------------------------------------------------------
Private Function FileCount(varFileList) As Long
Select Case VarType(varFileList)
Case vbBoolean
'User canceled out of the File Open dialog box.
FileCount = 0
Case vbString
'Dialog box is in single file mode.
'Single file selected for opening only.
FileCount = 1
Case vbArray + vbVariant
'Multiple files selected for processing.
FileCount = UBound(varFileList) - LBound(varFileList) + 1
End Select
End Function
'----------------------------------------------------------------------
Private Function CurrentFileName(varFileList As Variant, _
ilngFileNumber As Long) As String
Select Case VarType(varFileList)
Case vbBoolean
'User canceled out of the File Open dialog box.
CurrentFileName = ""
Case vbString
'Dialog box is in single file mode.
'Single file selected for opening only.
CurrentFileName = varFileList
Case vbArray + vbVariant
'Multiple files selected for processing.
'Return the filename currently pointed to.
CurrentFileName = CStr(varFileList(ilngFileNumber))
End Select
End Function
