Merge multiple workbooks containing singe workseets that have different headers but consolidate and exclude certain header values

[mikester]

Good Afternoon,

I have used a brilliant piece of code put together by forum member @p45cal from this thread CLICK and need some help fine tuning it to get it to be even more efficient.

This is the code:

Sub Consolidate()
Dim rngHdr As Range, HdrsToCopy As Range, DestRow As Range
Dim AllHeaders()
ReDim AllHeaders(0 To 0)
With ThisWorkbook
  Set DestSheet = .Sheets.Add(after:=.Sheets(.Sheets.Count))
End With  'thisworkbook
With DestSheet
  Set DestRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)  'or any other column.
End With  'DestSheet
filenames = Application.GetOpenFilename("Excel files,*.csv*", MultiSelect:=True)
If IsArray(filenames) Then
  For Each fName In filenames
    Set WkBk = Workbooks.Open(fName)
    For Each sht In WkBk.Sheets
      rowscount = sht.UsedRange.Rows.Count - 1
      For Each cll In sht.Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        NewHeader = False
        HeaderColumn = 0
        For i = LBound(AllHeaders) To UBound(AllHeaders)
          If AllHeaders(i) = cll.Value Then
            HeaderColumn = i
            Exit For
          End If
        Next i
        If HeaderColumn = 0 Then
          If UBound(AllHeaders) = 0 Then ReDim AllHeaders(1 To UBound(AllHeaders) + 1) Else ReDim Preserve AllHeaders(1 To UBound(AllHeaders) + 1)
          AllHeaders(UBound(AllHeaders)) = cll.Value
          HeaderColumn = UBound(AllHeaders)
          NewHeader = True
        End If
        If NewHeader Then DestSheet.Cells(1, HeaderColumn).Value = AllHeaders(HeaderColumn)
        cll.Offset(1).Resize(rowscount).Copy DestRow.Offset(, HeaderColumn - 1)
      Next cll
      Set DestRow = DestRow.Offset(rowscount)
    Next sht
    WkBk.Close False
  Next fName
End If
End Sub

I am using this macro to compile 10 to 50 workbooks at a time, each workbook has a single worksheet and is always .csv filetype. There are many rows of data in each workbook, there are effectively 12 unique "types" of workbook, by types I mean they've got differently worded headers or some have headers the others don't. The above code merges them all into a new worksheet perfectly. However what I'm left with is each unique header occupying its own column. For me these headers although rightly populated in unique columns are effectively duplicate columns as many of them represent the same value, its just these "types" of workbook I have call the same value by a slightly different word. For example one workbook may have a header called "ScanType" and another "MODE", each is exactly the same quality and needs to be in the same column with their respective data in my consolidated output after running this macro.

I've spent some time going through these 12 versions of the workbooks and noted down all the header values I want to merge together, their exact wording and what column I want them to show in e.g. " Network (MCC MNC)", "Network (MCC MNC)", "Network" all need to go in a single column called "Network_"
I can't change the output properties of the device producting these .csv files to bring any uniformity/correct the " "(space) before the header title so I believe consolidating the headers into single columns is my only option.

There are other headers I want to completey remove/exclude from the resulting consolidated worksheet, I would be interested to know if this could be made possible via the macro but it's not as crucial as the above 'merging' request as I can manually delete the unwanted columns.



I'm not sure where or what code to insert into the already working code I've detaied above to manage these headers so any guidance would be greatly received. thanks again to p45cal for the code he provided in the linked thread, it's already proved a benefit.