Solved: Macro to Combine Multiple Workbooks into one Workbook

[jo15765]

I Have about 15 workbooks each containing 2 sheets. I want to take each of the two sheets in this workbook, and combine them into one "Master" workbook. I have been working on a macro and am almost there, but am stumped. This is the code that I have thus far:

Dim varBooks
Dim varBook
Dim SName
Dim SNames
Dim wb As Excel.Workbook

varBooks = Array(All my workbook names)

    For Each varBook In varBooks
        Set wb = Workbooks.Open(Filename:="Workbooks location" & varBook)
        With wb
        ActiveWorkbook.Sheets.Select
        ActiveWindow.SelectedSheets.Copy
        With ActiveWorkbook
        .Close& ".xls"
        End With
    Next varBook

This will open each workbook in my array, and will copy the sheets into a new workbook. The problem is, it opens a new workbook for each workbook in the array. I want it to copy each sheet into ONE workbook.

Can someone please point out the error in my ways?!?!?!

[mancubus]

hi.

here is a way to do that:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=829

[Paul_Hossler]

I think always using ActiveWorkbook was confusing it

[VBA]
Option Explicit
Sub NotTested()
Dim varBooks
Dim varBook
Dim SName
Dim SNames
Dim wb As Workbook, wbFinal As Workbook
varBooks = Array("aaa.xlsx", "bbb.xlsx", "ccc.xlsx")
Set wbFinal = ActiveWorkbook
For Each varBook In varBooks
Set wb = Workbooks.Open(Filename:="Workbooks location" & varBook)
With wb
.Sheets.Select
ActiveWindow.SelectedSheets.Copy (wbFinal.Worksheets(1))
.Close
End With
Next varBook
End Sub
[/VBA]

Paul

[jo15765]

Hey Paul, thank you for the response. The code is throwing a debug error of
Run-time Error '438'

Object doesn't support this property or Method

ActiveWindow.SelectedSheets.Copy (wbFinal.Worksheets(1))

Any idea's what may be causing that? Is this possibly a reference that I am missing in my code?


Mancubus ---

I don't really understand that code that you sent the link to...How would I specify what order to copy the sheets in?

[mancubus]

[VBA] Application.EnableEvents = False 'disables other macros
Application.ScreenUpdating = False
path = GetDirectory 'enables yo to specify the directory of wb's to be imported
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName) 'opens the files in alphabetical order
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then 'makes sure ws has data in it
Else
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 'copies the ws's in wb as last ws that is running macro
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
[/VBA]

[Paul_Hossler]

ActiveWindow.SelectedSheets.Copy (wbFinal.Worksheets(1))

I didn't really look at that part before, but that's not the way to use SelectedSheets, and I missed it.

#1 uses SelectedSheets correctly, but if you want to copy all the sheets, there's no need to select them first, so #2 just loops them

BTW, I always like to explicitly define my variables when I Dim them, and the same for using Option Explicit

[VBA]

Sub NotTested1()
Dim varBooks As Variant, varBook As Variant
Dim ws As Worksheet
Dim wb As Workbook, wbFinal As Workbook

varBooks = Array("aaa.xlsx", "bbb.xlsx", "ccc.xlsx")

Set wbFinal = ActiveWorkbook
For Each varBook In varBooks
Set wb = Workbooks.Open(Filename:="Workbooks location" & varBook)
With wb
.Sheets.Select

For Each ws In ActiveWindow.SelectedSheets
ws.Copy (wbFinal.Worksheets(1))
Next

.Close
End With
Next varBook
End Sub
Sub NotTested2()
Dim varBooks As Variant, varBook As Variant
Dim ws As Worksheet
Dim wb As Workbook, wbFinal As Workbook

varBooks = Array("aaa.xlsx", "bbb.xlsx", "ccc.xlsx")

Set wbFinal = ActiveWorkbook
For Each varBook In varBooks
Set wb = Workbooks.Open(Filename:="Workbooks location" & varBook)
With wb
For Each ws In wb.Worksheets
ws.Copy (wbFinal.Worksheets(1))
Next

.Close
End With
Next varBook
End Sub
[/VBA]

Paul

[jo15765]

Paul --

On this line of code:

                ws.Copy (wbFinal.Worksheets(1))

I get a debug error of object doesn't support this property or method

[Paul_Hossler]

OK, this time i created some dummy WB's and ran the macro


[VBA]
Option Explicit
Const sLocation As String = "C:\Users\Daddy\Desktop\"

Sub NotTested3()
Dim varBooks As Variant, varBook As Variant
Dim ws As Worksheet
Dim wb As Workbook, wbFinal As Workbook

varBooks = Array("book2.xlsx", "book3.xlsx", "book4.xlsx")

Application.ScreenUpdating = False
Set wbFinal = ActiveWorkbook
For Each varBook In varBooks
Set wb = Workbooks.Open(Filename:=sLocation & varBook)
With wb
For Each ws In wb.Worksheets
'------------------- forgot the Call
'compiles w/o it, just doesn't run :-)
Call ws.Copy(wbFinal.Worksheets(1))
Next

.Close
End With
Next varBook
Application.ScreenUpdating = True

End Sub
[/VBA]

Paul

[GTO]

Hi all,

I see Paul just caught the parenthesis w/o the Call, so skipping that, here's w/o looping the sheets.

[VBA]Sub Example()
Dim varBooks As Variant
Dim wb As Workbook, wbFinal As Workbook
Dim i As Long
Const Path As String = "C:\Documents and Settings\MARK\Desktop\DURANGO Forms\2011-10-17\"

varBooks = Array("ErrorHandling.xls", "KillMods.xls", "SUMPRODUCT4Pascal2.xls")

Set wbFinal = ActiveWorkbook
Application.EnableEvents = False
For i = LBound(varBooks, 1) To UBound(varBooks, 1)
Set wb = Workbooks.Open(Path & varBooks(i))
wb.Worksheets.Copy wbFinal.Worksheets(1)
wb.Close False
Next
Application.EnableEvents = True
End Sub[/VBA]

Mark

[jo15765]

Mark that worked!!

ALL thank you for the help!!

[GTO]

No problem, but that all was Paul's code. I think we caught the parenthesis (or lack of Call) at about the same time, and I happened to recall "discovering" (I'm sure I'm the umpteenth millionth person to make this particular 'find', so not exactly Columbus' or the Vikings' level of discovery) that you could copy all at once. I don't believe I ever tested against a wb with any non-worksheet sheets, so that might be an issue if you use charts.

[Paul_Hossler]

[VBA]
wb.Worksheets.Copy wbFinal.Worksheets(1)
[/VBA]

Clever

I didn't know you could do that

Paul

[jo15765]

Paul and GTO thank the both of you for help! IT is functioning as needed, and thank you for your patience as I attempt to teach myself VBA.

[GTO]

Paul and GTO thank the both of you for help! IT is functioning as needed, and thank you for your patience as I attempt to teach myself VBA.

Hi jo,

Happy to be of help and glad you got 'er working .

[vba]
wb.Worksheets.Copy wbFinal.Worksheets(1)
[/vba]

Clever

I didn't know you could do that

Paul

Thanks Paul. Me neither until I just tried it one day. I must admit, I was tickled when it actually worked . Somehow its always a "Cool!" moment when we "discover" something, even a little thing.

Mark

[Dazvedania]

GTO-


The array is intended to point at the specific workbooks needed, correct? For some reason it is combining my path location with the arrays
and causing it to not be able to find the path. Is there a way of pulling this off either without the array or by combining all workbooks within that path?
I organized all of the folders of which I need to combine into the same path in order to avoid getting too specific.

[GTO]

Greetings and welcome to vbaexpress

GTO-

The array is intended to point at the specific workbooks needed, correct?

Yes.

...For some reason it is combining my path location with the arrays
and causing it to not be able to find the path. Is there a way of pulling this off either without the array or by combining all workbooks within that path?
I organized all of the folders of which I need to combine into the same path in order to avoid getting too specific.

I am afraid you lost me a bit. As I read it: We need the path and wb name concatenated to provide the fullname, in order to open the wb.

As to "combining all workbooks within that path", if you mean all workbooks in a particular folder, or, all workbooks in a particular folder and that folders child folders - yes, we could do that. I am guessing that maybe the last option is what you want, based upon your final sentence.

I would suggest posting the code you have.

Mark

[GTO]

ACK!

Sorry, I just realized that it would probably also be much better to start your own thread. Although your issue may be very similar, it can be confusing to have to wade through a bunch of old posts to get to a new question in an old thread. Hope that makes sense.

Mark