Since your workbooks only have 1 worksheet, moving the sheet would create a
workbook with no sheets (not possible). To get around this, try changing this
line:

Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)

to

Sheets().Copy After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)

the effect will be similar, the one difference being that the old workbook
will still contain a copy of the worksheet.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Mrs. Robinson" wrote:

I am using this macro to merge 50+ workbooks with 1 sheet per wb, into one
workbook. I get this error message: Method 'Move' of object 'Sheets'
failed. Can you help? Thanks...

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer

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)
Workbooks.Open FileName:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub