Code to combine workbooks

[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

 

[Tom Ogilvy]

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

 

[Andy Soho]

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

 

[Dave Peterson]

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.

 

[David Turner]

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

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("D210").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?

 

[Ken Fickling]

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.