Consolidating Excel files according to Row Heading.

[Murali K]

I have a situation here.I got 30-40 files.I have to consolidate it.I have used a Following VBA for it:


Private Sub ConcatenateAll()

Workbooks.Add
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="H:\ConcatResults.xls"

CopyTargetBookmark = 1

For Each Workbook In Application.Workbooks
If Workbook.Name <> "ConcatResults.xls" And Workbook.Name <> "PERSONAL.XLS" Then
Workbook.Activate
Workbook.Worksheets(1).UsedRange.Copy
Workbooks("ConcatResults.xls").Activate
Range("A" & CopyTargetBookmark).Select
ActiveSheet.Paste
CopyTargetBookmark = CopyTargetBookmark + Workbook.Worksheets(1).UsedRange.Rows.Count
End If
Next Workbook

End Sub


But by using this i am facing a problem.I want only selected columns to be consolidated.Example:

Suppose row heading is
County Member name Address Cost Building # replacement cost

And this heading is present in all excel files which I want to consolidate, but some extra columns are also present in between.I dont want those extra columns.Please help me out....Thanks in advance!!!