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

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
>

Joel

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

"joel" <(E-Mail Removed)> wrote in message
newsBAB1892-6B1F-4E10-8A83-(E-Mail Removed)...
> 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
>>

Joel

When I run it it errors out on the item below

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

"Sam Commar" <(E-Mail Removed)> wrote in message
news:33A905BE-8C52-45D5-9F97-(E-Mail Removed)...
> Joel
>
> Thanks so much . I really appreciate it. Ill test it right now.
>
> "joel" <(E-Mail Removed)> wrote in message
> newsBAB1892-6B1F-4E10-8A83-(E-Mail Removed)...
>> 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
>>>

Sorry the error it gives is

Run time error '424'
Object required

"Sam Commar" <(E-Mail Removed)> wrote in message
news:E9AE9A48-05C6-4964-9319-(E-Mail Removed)...
> Joel
>
> When I run it it errors out on the item below
>
> newbk.SaveAs Filename:=sf & "\" & _
> sf.Name & ".xls"
>
> "Sam Commar" <(E-Mail Removed)> wrote in message
> news:33A905BE-8C52-45D5-9F97-(E-Mail Removed)...
>> Joel
>>
>> Thanks so much . I really appreciate it. Ill test it right now.
>>
>> "joel" <(E-Mail Removed)> wrote in message
>> newsBAB1892-6B1F-4E10-8A83-(E-Mail Removed)...
>>> 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
>>>>