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

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

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