Combining several workbooks into one workbook
 

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

Combining several workbooks into one workbook
 

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. )

--
Regards,
Tom Ogilvy


"Betty" wrote:

This is the error that I'm getting now

---------------------------
Microsoft Excel
---------------------------
Copy method of Worksheet class failed
---------------------------
OK
---------------------------


"Tom Ogilvy" wrote:

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


for x = 1 to UBound(FilesToOpen)
set bk = Workbooks.Open(FileName:=FilesToOpen(x))
bk.Sheets(1).copy After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)

bk.close saveChanges:=False
'optional
FilesToOPen(x).Kill
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

--
Regards,
Tom Ogilvy

"Betty" wrote:

I have several (24 in total) workbooks and each workbook only contains one
worksheet and I think that's the problem.

Thanks

"Tom Ogilvy" wrote:

What are you trying to accomplish he
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.

--
Regards,
Tom Ogilvy





"Betty" wrote:

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

Combining several workbooks into one workbook
 

What are you trying to accomplish he
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.

--
Regards,
Tom Ogilvy





"Betty" wrote:

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

Combining several workbooks into one workbook
 

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


"Tom Ogilvy" wrote:

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. )

--
Regards,
Tom Ogilvy


"Betty" wrote:

This is the error that I'm getting now

---------------------------
Microsoft Excel
---------------------------
Copy method of Worksheet class failed
---------------------------
OK
---------------------------


"Tom Ogilvy" wrote:

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


for x = 1 to UBound(FilesToOpen)
set bk = Workbooks.Open(FileName:=FilesToOpen(x))
bk.Sheets(1).copy After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)

bk.close saveChanges:=False
'optional
FilesToOPen(x).Kill
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

--
Regards,
Tom Ogilvy

"Betty" wrote:

I have several (24 in total) workbooks and each workbook only contains one
worksheet and I think that's the problem.

Thanks

"Tom Ogilvy" wrote:

What are you trying to accomplish he
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.

--
Regards,
Tom Ogilvy





"Betty" wrote:

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