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