Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining two workbooks | Excel Discussion (Misc queries) | |||
Combining Workbooks | Charts and Charting in Excel | |||
Combining several workbooks into one workbook | Excel Programming | |||
combining workbooks | Excel Worksheet Functions | |||
Combining workbooks into one????? | Excel Programming |