Combine files as tabs in a single Workbook

  • Thread starter Thread starter Sam Commar
  • Start date Start date
S

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
 
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
 
Joel

When I run it it errors out on the item below

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top