What is the best way to combine multiple workbooks into one?

[paazan]

Hello,

As the title says, I'm trying to find the best way to combine information stored in multiple workbooks into a separate one.

Say that I have Workbook1, Workbook2, Workbook3,..... etc.

Now I'm trying to build a new workbook called "Combined". In this "Combined" workbook, I want it to load a couple columns from Workbook1.Sheet1 into "Combined".Sheet1.Column A-C, and then some other columns from Workbook2.Sheet1 into "Combined".Sheet1.Column D-E.

The general idea is this "Combined" workbook take information from different sources and form a new big sheet that serves as a database, which later might feed information to other workbooks.

I know I can use something as simple as using "=" in a cell that links to a certain cell in another workbook. My concern is if I have larger and growing data later, I will need a better way to link these workbooks, sheets, and columns.

It will be a great help if you can provide a couple different ways that are available for this kind of task, including VBA if needed.

Thanks for reading.

[Aussiebear]

This code from Jake should do the trick


Option Explicit

Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub