Merging two or more files, column by column

[dmac04]

Hi,

I'm a total beginner when it comes to VBA, and I have a (to me at least) confusing problem. I have an arbitrary number (let's say three or so) Excel workbooks that need to have their contents merged into another, larger workbook.

Each of the smaller Excel files have three columns of data that I need to add to separate worksheets in the larger workbook. Each smaller file will have say, an A, B and C column, and I need to move these columns to the larger workbook, which will have all of the A columns on worksheet A, all of the B columns on worksheet B, and so on.

My question is, is there any sample code out there for a task like this floating around that I could look at?

[patel]

[VBA]Sub MergeColumns()
Set DestWB = ActiveWorkbook
Dim DestCell As Range
DataColumn = "A"
FileNames = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls*),*.xls*", _
Title:="Select the workbooks to merge.", MultiSelect:=True)
If IsArray(FileNames) = False Then
If FileNames = False Then
Exit Sub
End If
End If
dcol = 1
For N = LBound(FileNames) To UBound(FileNames)
Set WB = Workbooks.Open(Filename:=FileNames(N), ReadOnly:=True)
Set WS = WB.Sheets(1)
With WS
.Select
If .UsedRange.Cells.Count > 1 Then
Lastcol = .UsedRange.Columns.Count
.UsedRange.Copy DestWB.Sheets(1).Cells(1, dcol)
dcol = Lastcol + 1
End If
End With
WB.Close savechanges:=False
Next N
End Sub[/VBA]