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 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.
> > > >
> > > > --
> > > > 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
> > > > >
|
Similar Threads
