Combining several workbooks into one workbook

[Betty]

While trying to run this macro, I get this error. Any insights?

Thanks
---------------------------
Microsoft Excel
---------------------------
Method 'Move' of object 'Sheets' failed
---------------------------
OK
---------------------------

Here is the Syntax

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

 

[Tom Ogilvy]

Comment out your error handler so you can see what the situation is. When
you get the error message, choose debug.

then see where it stops and what the value of your variables are.

It worked for me with vanilla workbooks. I saw problems with workbooks
that had links and workbooks that were password protected.

Anyway, the basic code appears to be sound and no you have to deal with
peculiarities/special situations in your workbooks.

(I assume you replaced the Wend with a Next. )

 

[Tom Ogilvy]

What are you trying to accomplish here:
Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)


Sheets() is a problem. You have to specify which sheet you want to move.

perhaps

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


Although I if there is only one sheet in the workbook, then I don't believe
you can move it to another workbook. Perhaps use copy in that case and
delete all the workbooks after.

 

[Betty]

Yes I did.
Thank you so much for your help.

Comment out your error handler so you can see what the situation is. When
you get the error message, choose debug.

then see where it stops and what the value of your variables are.

It worked for me with vanilla workbooks. I saw problems with workbooks
that had links and workbooks that were password protected.

Anyway, the basic code appears to be sound and no you have to deal with
peculiarities/special situations in your workbooks.

(I assume you replaced the Wend with a Next. )