Compare Column headers across multiple Sheets in same workbook

[blbat]

I want to check just the Header Row for each sheet in a single workbook against a static array of Header names, My code works to find what's missing, how do I account for headers that are extra, and put a msgbox up that tells the user there's an extra column, or columns, on a given sheet?

Sub CheckHeadersAllSheets()

    Dim destinationSheet As Worksheet, destRow As Long, destCol As Variant
    Dim ws As Worksheet, wsRow As Long, wsCol As Variant
    Dim columnHeader As Variant
   
    Set destinationSheet = ThisWorkbook.Worksheets("Master")
   
    For Each ws In ThisWorkbook.Worksheets
        If Not ws Is destinationSheet Then
            For Each columnHeader In Array("aic", "_job", "_sumry", "mpreview", "equipment", "serial", "system", "mpnbr", "mrc", "periodicty", "procedure", "tech", "notes", "Name", "Type")

                With ws
                    wsCol = Application.Match(columnHeader, .Rows(1), 0)
                    If Not IsError(wsCol) Then
                        wsRow = .Cells(.Rows.Count, wsCol).End(xlUp).Row
                        destCol = Application.Match(columnHeader, destinationSheet.Rows(1), 0)
                        destRow = destinationSheet.Cells(destinationSheet.Rows.Count, destCol).End(xlUp).Row + 1
                      
                    Else
                        MsgBox "Column heading " & columnHeader & " not found in row 1 of " & .Name
                    End If
                End With
            Next
        End If
    Next
       
End Sub

 

[HaHoBe]

Hi bibat,

maybe compare the headers from each sheet to the master sheet as well? Code may look like this:

Sub CheckHeadersAllSheets_mod()

  Dim wksStart          As Worksheet
  Dim wksSheet          As Worksheet
  Dim varwksRow         As Variant
  Dim varwksCol         As Variant
  Dim varColHeader      As Variant
  Dim lngLastCol        As Long
  
  Set wksStart = ThisWorkbook.Worksheets("Daten")
  
  With wksStart
    lngLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
  End With
    
  For Each wksSheet In ThisWorkbook.Worksheets
    If Not wksSheet Is wksStart Then
      For Each varColHeader In wksStart.Range("A1").Resize(1, lngLastCol)
        With wksSheet
          varwksCol = Application.Match(varColHeader, .Rows(1), 0)
          If IsError(varwksCol) Then
            MsgBox "Column heading '" & varColHeader & "' not found in row 1 of " & .Name, , "Column Header not found"
          End If
        End With
      Next varColHeader
      For varwksCol = 1 To wksSheet.Cells(1, wksSheet.Columns.Count).End(xlToLeft).Column
        varwksRow = Application.Match(wksSheet.Cells(1, varwksCol).Value, wksStart.Rows(1), 0)
        If IsError(varwksRow) Then
          MsgBox "Column heading '" & wksSheet.Cells(1, varwksCol).Value & "' is added in " & wksSheet.Name, , "Additional Header found"
        End If
      Next varwksCol
    End If
  Next wksSheet
  
  Set wksSheet = Nothing
      
End Sub

Ciao,
Holger

 

[blbat]

Hi bibat,

maybe compare the headers from each sheet to the master sheet as well? Code may look like this:

Sub CheckHeadersAllSheets_mod()

  Dim wksStart          As Worksheet
  Dim wksSheet          As Worksheet
  Dim varwksRow         As Variant
  Dim varwksCol         As Variant
  Dim varColHeader      As Variant
  Dim lngLastCol        As Long
 
  Set wksStart = ThisWorkbook.Worksheets("Daten")
 
  With wksStart
    lngLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
  End With
   
  For Each wksSheet In ThisWorkbook.Worksheets
    If Not wksSheet Is wksStart Then
      For Each varColHeader In wksStart.Range("A1").Resize(1, lngLastCol)
        With wksSheet
          varwksCol = Application.Match(varColHeader, .Rows(1), 0)
          If IsError(varwksCol) Then
            MsgBox "Column heading '" & varColHeader & "' not found in row 1 of " & .Name, , "Column Header not found"
          End If
        End With
      Next varColHeader
      For varwksCol = 1 To wksSheet.Cells(1, wksSheet.Columns.Count).End(xlToLeft).Column
        varwksRow = Application.Match(wksSheet.Cells(1, varwksCol).Value, wksStart.Rows(1), 0)
        If IsError(varwksRow) Then
          MsgBox "Column heading '" & wksSheet.Cells(1, varwksCol).Value & "' is added in " & wksSheet.Name, , "Additional Header found"
        End If
      Next varwksCol
    End If
  Next wksSheet
 
  Set wksSheet = Nothing
     
End Sub

Ciao,
Holger

Thanks...I will see if I can adapt some of this to work.