Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine worksheets in multiple workbook in one workbook with a macro
I was provided the following macro to combine multiple workbook sheets in
one sheet however I am getting the error -"Run time error 424" Object required on the lines below newbk.SaveAs Filename:=sf & "\" & _ sf.Name & ".xls" I would really apprceiate if someone can guide me on what the fix of this error might be. --------- Please see complete macro below. The macro below will search each folder in the Root directory and combine all sheets in all workbook into a single workbook. then it will save the new book in the same directory using the parent folders name. Sub Combinebooks() Root = "c:\Temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(Root) For Each sf In folder.subfolders First = True FName = Dir(sf & "\*.xls") Do While FName < "" Set bk = Workbooks.Open(Filename:=sf & "\" & FName) For Each sht In bk.Sheets If First = True Then sht.Copy Set newbk = ActiveWorkbook First = False Else With newbk sht.Copy _ after:=.Sheets(.Sheets.Count) End With End If Next sht bk.Close savechanges:=False FName = Dir() Loop newbk.SaveAs Filename:=sf & "\" & _ sf.Name & ".xls" newbk.Close Next sf End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine worksheets in multiple workbook in one workbook with a macro
Untested, but may be what is needed... newbk.SaveAs Filename:=sf.Path & "\" & sf.Name & ".xls" -- Jim Cone Portland, Oregon USA "Sam Commar" wrote in message I was provided the following macro to combine multiple workbook sheets in one sheet however I am getting the error -"Run time error 424" Object required on the lines below newbk.SaveAs Filename:=sf & "\" & _ sf.Name & ".xls" I would really apprceiate if someone can guide me on what the fix of this error might be. --------- Please see complete macro below. The macro below will search each folder in the Root directory and combine all sheets in all workbook into a single workbook. then it will save the new book in the same directory using the parent folders name. -snip- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine worksheets in multiple workbook in one workbook with a macro
Check your other post.
Sam Commar wrote: I was provided the following macro to combine multiple workbook sheets in one sheet however I am getting the error -"Run time error 424" Object required on the lines below newbk.SaveAs Filename:=sf & "\" & _ sf.Name & ".xls" I would really apprceiate if someone can guide me on what the fix of this error might be. --------- Please see complete macro below. The macro below will search each folder in the Root directory and combine all sheets in all workbook into a single workbook. then it will save the new book in the same directory using the parent folders name. Sub Combinebooks() Root = "c:\Temp" Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(Root) For Each sf In folder.subfolders First = True FName = Dir(sf & "\*.xls") Do While FName < "" Set bk = Workbooks.Open(Filename:=sf & "\" & FName) For Each sht In bk.Sheets If First = True Then sht.Copy Set newbk = ActiveWorkbook First = False Else With newbk sht.Copy _ after:=.Sheets(.Sheets.Count) End With End If Next sht bk.Close savechanges:=False FName = Dir() Loop newbk.SaveAs Filename:=sf & "\" & _ sf.Name & ".xls" newbk.Close Next sf End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple workbooks into one workbook | Excel Discussion (Misc queries) | |||
how can I combine multiple worksheets into a single workbook? | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into one workbook | Excel Discussion (Misc queries) |