Try uncommenting this line:
'msgbox "Nothing found in this folder: " & sf

Maybe it'll give you an idea what's going wrong.

Sam Commar wrote:

Dave

Thanks for the info. I did the modification and although this did not give
me the error it did not seem to do anything.
The macro references C:\temp

Do the excel files have to be in the C:\temp folder.

Also I am using Excel 2007

Thanks

S Commar

"Dave Peterson" wrote in message
...
Sub Combinebooks()

Root = "c:\Temp"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(Root)

For Each sf In folder.subfolders
First = True
set newbk = nothing '<-- added
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

if newbk is nothing then
'do nothing or maybe a msgbox
'msgbox "Nothing found in this folder: " & sf
else
newbk.SaveAs Filename:=sf & "\" & _
sf.Name & ".xls"
newbk.Close
end if
Next sf

End Sub

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

--

Dave Peterson