I usually do something like this with a formula to make named ranges dynamic.
There are rules on valid names
Option Explicit
Sub test()
Dim iCol As Long, i As Long
For iCol = 1 To ActiveSheet.Cells(1, 1).End(xlToRight).Column
Call NameAddDynamic(ActiveSheet.Cells(1, iCol))
Next iCol
With ActiveWorkbook
For i = 1 To .Names.Count
MsgBox .Names(i).Name & " --- " & .Names(i).RefersTo & " --- " & .Names(i).RefersToRange.Address
Next i
End With
End Sub
Sub NameAddDynamic(R As Range)
Dim sFormula As String, sName As String
Dim rName As Range
Set rName = R.Cells(1, 1)
sName = MakeASCII(rName.Value)
With R.Parent
sFormula = "=OFFSET("
sFormula = sFormula & "'" & .Name & "'!" & rName.Address(True, True) & ",0,0,"
sFormula = sFormula & "COUNTA("
sFormula = sFormula & "'" & .Name & "'!" & rName.EntireColumn.Address(True, True) & "),1)"
.Parent.Names.Add Name:=sName, RefersTo:=sFormula
End With
End Sub
Private Function MakeASCII(S As String) As String
Dim i As Long
Dim s1 As String, c As String
c = Left(S, 1)
Select Case c
Case "0" To "9"
S = "_" & S
Case Else
S = S
End Select
For i = 1 To Len(S)
c = Mid(S, i, 1)
Select Case c
Case "a" To "z", "A" To "Z", "0" To "9", "_"
s1 = s1 & c
Case " "
s1 = s1 & "_"
End Select
Next i
MakeASCII = s1
End Function