Code to combine workbooks

  • Thread starter Thread starter Andy Soho
  • Start date Start date
A

Andy Soho

Hi,

I need to combine several workbooks into one workbook. Say, individual
workbooks are named A, B and C, the master workbook is Master. They all have
the same format and are stored in directory c:\Data. Please can anyone help
and send me the code

Thanks
Andy
 
Sub GrabData()

Dim varr As Variant
Dim rng As Range
Dim i As Long
Dim sh As Worksheet
Dim wkbk as Workbook
varr = Array("A.xls", "B.xls", "c.xls")
Set sh = Workbooks("Master.xls").Worksheets(1)
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open("c:\Data\" & varr(i))
Set rng = wkbk.Worksheets(1). _
UsedRange
rng.Copy sh. _
Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub


You talk about combining workbooks, but don't say how many sheets are in the
books or how you want them combined.

The above assumes data from the first sheet in each book to one sheet in
Master.

Regards,
Tom Ogilvy
 
Thanks Tom,

There only one sheet in each workbook. Your code works perferctly. Thanks.
One more question, if I'm to put the name of workbook A, B & C files on a
range in the master workbook, how should the line varr = array("A.xls",
"B.xls", "C.xls") be re-phrased. This will give me flexibility and don't
need to change the code when new file is added.

Thanks
Andy
 
I'm not Tom, but one way would be like:

Option Explicit

Sub GrabData()

Dim varr As Variant
Dim rng As Range
Dim i As Long
Dim sh As Worksheet
Dim wkbk As Workbook
With Workbooks("master.xls").Worksheets("Sheet2")
varr = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
'varr = Array("A.xls", "B.xls", "c.xls")
Set sh = Workbooks("Master.xls").Worksheets("sheet1")
For i = LBound(varr, 1) To UBound(varr, 1)
Set wkbk = Workbooks.Open("C:\data\" & varr(i, 1))
Set rng = wkbk.Worksheets(1). _
UsedRange
rng.Copy sh. _
Cells(Rows.Count, 1).End(xlUp)(2)
wkbk.Close savechanges:=False
Next
End Sub


I assumed that master.xls sheet2 held the data (a1:a (lastusedcell)). And I
changed the combined worksheet to sheet1.
 
Tom Ogilvy wrote
Sub GrabData()

Dim varr As Variant
Dim rng As Range
Dim i As Long
Dim sh As Worksheet
Dim wkbk as Workbook
varr = Array("A.xls", "B.xls", "c.xls")
Set sh = Workbooks("Master.xls").Worksheets(1)
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open("c:\Data\" & varr(i))
Set rng = wkbk.Worksheets(1). _
UsedRange
rng.Copy sh. _
Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub
Click to expand...

Tom,

I looked at your code with interest because I have a similar need, but I
only need certain data imported into respective column areas of the
master file. I recorded the manual steps required to accomplish this
using your file names (slightly different paths):

Sub GrabData()
Workbooks.Open Filename:="C:\Data\EXCEL\A.xls"
Range("B2:B10").Select
Selection.Copy
Windows("Master.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Workbooks.Open Filename:="C:\Data\EXCEL\B.xls"
Range("C2:C10").Select
Selection.Copy
Windows("Master.xls").Activate
Range("C2").Select
ActiveSheet.Paste
Workbooks.Open Filename:="C:\Data\EXCEL\C.xls"
Range("D2:D10").Select
Selection.Copy
Windows("Master.xls").Activate
Range("D2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Can you help me (translated "provide me with") code similar to yours to
do what I want?
 
I have a similar need. I have 3 seperate workbooks I
want to combine. They all have 1 identical sheet "Index".
Each has about 10 other sheets which I want to
consolidate into 1 book, but they don't need to be
altered. The only hitch is that they all share a large
set of defined names. If I move or copy sheets, they get
screwed up. thx.
 
Dave Peterson wrote
I'm still not Tom, but here's one way:
Click to expand...

Another option I've considered (and tried with success) is to paste links
to the source files' cells into the master file and have the user answer
'Yes' when prompted to update them on opening the file.
 
Hi Dave,

What a great night I'm having, just came back from watching Terminator 3
(great movie) and now got your code.
It's always been great enjoyment to read this newsgroup

Thanks
Andy
 
If in fact your workbooks contain one sheet and you are not VBA savvy, you can use bulk file merger to do the consolidadation of your files. I managed to merge over 400 xls files to one master file in minutes with this utility program.
 
Back
Top