I'm not having any luck with any of these solutions. I"m going to keep
plugging away at it. Thanks for all the suggestions!


"JP" wrote:

I put four one-sheet workbooks on my desktop, then created a new
workbook and ran the following macro (basically an amalgam of the
edits Luke and John already posted). I selected the four (closed)
workbooks, and it copied the sheets from each workbook into the
current workbook.

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer
Dim wb As Excel.Workbook

On Error GoTo ErrHandler
Application.ScreenUpdating = False


FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
While x <= UBound(FilesToOpen)
Set wb = Workbooks.Open(Filename:=FilesToOpen(x))
wb.Sheets.Copy After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
wb.Close False
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub


--JP


On Oct 7, 11:33 am, Mrs. Robinson
wrote:
that didn't work either.



"JP" wrote:
My bad, Luke is right, you can't move all the sheets out of a
workbook.

Here's another suggestion: start x at zero, I believe LBound
(FilesToOpen) should start at zero. Otherwise I'm missing something. I
haven't been actually testing the code.

--JP