Combine files as tabs in a single Workbook
 

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

Combine files as tabs in a single Workbook
 

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" wrote:

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

Combine files as tabs in a single Workbook
 

Joel

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

"joel" wrote in message
...
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" wrote:

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

Combine files as tabs in a single Workbook
 

Joel

When I run it it errors out on the item below

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

"Sam Commar" wrote in message
...
Joel

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

"joel" wrote in message
...
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" wrote:

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

Combine files as tabs in a single Workbook
 

Sorry the error it gives is

Run time error '424'
Object required

"Sam Commar" wrote in message
...
Joel

When I run it it errors out on the item below

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

"Sam Commar" wrote in message
...
Joel

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

"joel" wrote in message
...
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" wrote:

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