I have googled for hours to "Change header names by mapping array of old header names to array of new header names"

The closest I have come is the code below that mappes a array of names to the first n cells

I need to add a second array to the code Newheadervalues = Array("Fielda", "Fieldb", "Fieldc") and map the Oldheadervalues array to the Newheadervalues array

Thanks


Sub TestHeaderRow1()
  On Error GoTo ErrorHandler
 
  Dim headerValues As Variant
  Dim sht As Excel.Worksheet
 
  headerValues = Array("Field1", "Field2", "Field3")
 
  Set sht = ActiveSheet
 
  Call PopulateHeaderRow(headerValues, sht)
 
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Sub PopulateHeaderRow(headerValues As Variant, sht As Excel.Worksheet)
 
Dim rngCount As Long
Dim rngHeader As Excel.Range
 
  ' count number of values to determine number of header cells being used
  rngCount = UBound(headerValues) + 1
 
  ' set range reference to exact number of header cells
  Set rngHeader = sht.Range(sht.Cells(1, 1), sht.Cells(1, rngCount))
 
  ' write array values to header
  rngHeader.Value = headerValues
 
End Sub