Find and rename Column Header using VBA

[Niclal]

Hello,
I have an Excel table with many columns and I want to use an Excel macro to rename the column header, e.g.
- Family Number to "Family no."
- Entry Date to "Reference Time"
- Common Identifier to "Reference Number"
- Brief Description to "Concise Summary"
- Subject Matter to "Main Theme"
Thank you so much for your help.
Niclal

[mancubus]

welcome to the forum.

did you consider doing it manually?
it will take shorter...

[Niclal]

Hi Mancubus,
You are right, but this is just a small part of a long Excel macro.
N

[mancubus]

since uploaded file contains only a small table here is my solution:

add a worksheet yo your workbook.
rename it as column_headers
from A1 downwards insert existing column headers.
from B1 downwards insert desired column headers.

run below macro



Sub vbax_57596_Change_Column_Headers()

Dim ColHeads
Dim i As Long

ColHeads = Worksheets("column_headers").Cells(1).CurrentRegion.Value

With Worksheets("Sheet1")
For i = LBound(ColHeads, 1) To UBound(ColHeads, 1)
.Rows(1).Find(ColHeads(i, 1)).Value = ColHeads(i, 2)
Next i
End With

End Sub


see attached.

make sure column A in sheet column_headers contains all values from row 1 in sheet Sheet1
if there are columns whose headers will not be changed then exclude these headers in sheet column_headers.