About Us

Ron de Bruin

Try
http://www.rondebruin.nl/fso.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Mrs. Robinson" wrote in message ...
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

On Oct 7, 10:30 am, Mrs. Robinson
wrote:
For Luke's solution, I get the same error message except it readsmethod
'Move'...failed

John's solution gives me a "Move method of Worksheet class failed" message.

JP's solution - there's some sort of error in this line: Set wb =
Workbooks.Open FileName:=FilesToOpen(x)

"john" wrote:
made small change to your code - see if does what you want.

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer
Dim wb As 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(1).Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1

wb.Close False
Wend

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
--
jb

"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- Hide quoted text -

- Show quoted text -

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Completely Merge Multiple Workbooks?	Daystrom	Excel Discussion (Misc queries)	0	March 30th 09 06:33 PM
Merge workbooks	Bonnie	Excel Discussion (Misc queries)	1	October 29th 07 11:50 PM
Merge Multiple Workbooks	ir26121973	Excel Programming	11	April 11th 07 06:55 PM
merge workbooks	FYF	Excel Worksheet Functions	2	December 13th 06 06:50 PM
merge workbooks	mg_sv_r	Excel Worksheet Functions	1	June 14th 06 05:16 PM

Menu

About Us