Combine files as tabs in a single Workbook

[Sam Commar]

I have 3 Excel files in a folder. I wanted to setup some kind of macro or
programming to combine the three files as three tabs in a single file. Could
someone kindly assist me on this.
2 of the excel files have 2 tabs each and one file has 1 tab. So when they
are combined it makes one file with 5 tabs.


In effect I will have 20 folder each with 3 files -structure will be the
same that is 2 files with 2 tabs and 1 file with one tab and they are to be
made into one file with tabs.



Thanks so much for your help

Sam Commar

 

[joel]

The macro below will seach each folder in the Root directroy and combine all
sheets in all workbook into a single workbook. then it will save the new
book in the same directroy 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

 

[Sam Commar]

Joel

Thanks so much . I really appreciate it. Ill test it right now.

 

[Sam Commar]

Joel

When I run it it errors out on the item below

newbk.SaveAs Filename:=sf & "\" & _
sf.Name & ".xls"

 

[Sam Commar]

Sorry the error it gives is

Run time error '424'
Object required