Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Merge multiple workbooks into one.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Merge multiple workbooks into one.
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Merge multiple workbooks into one.
My guess is that you have an unqualified reference. Try setting an
object reference to the newly opened workbook, then fully qualifying
the Move method.

Dim wb As Excel.Workbook
Set wb = Workbooks.Open FileName:=FilesToOpen(x)
wb.Sheets.Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)

--JP
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,722
Default Merge multiple workbooks into one.
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default Merge multiple workbooks into one.
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Merge multiple workbooks into one.
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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default Merge multiple workbooks into one.
try changing this line:

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

for this

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


--
jb


"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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Merge multiple workbooks into one.
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 -

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Merge multiple workbooks into one.
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 -


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 897
Default Merge multiple workbooks into one.
I put four one-sheet workbooks on my desktop, then created a new
workbook and ran the following macro (basically an amalgam of the
edits Luke and John already posted). I selected the four (closed)
workbooks, and it copied the sheets from each workbook into the
current workbook.

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

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub


--JP


On Oct 7, 11:33*am, Mrs. Robinson
wrote:
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

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Merge multiple workbooks into one.
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 -


Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Merge multiple workbooks into one.
I get copy method of worksheet class failed error.

"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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

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


All times are GMT +1. The time now is 05:32 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement