I are coming to believe that should be a "Best Practice" to avoid the use of Worksheet.Names in code.
For example. Always change the Sheet's Code Name, then use only it, vice using the Sheets/Worksheets Collection.
In the case of Range names, I think it may be best to use the applicable sheet's Code as a Property_Get or a Public Function to return Ranges.
Example:
Sheets("index").CodeName = "IndexSht"
IndexSht Code(s)
Public Function GetRange_NoHeaderPlus1(HeaderName As String) As Object
Dim Col As Range
Set Col = Range(1:1).Find(HeaderName).EntireColumn
Set GetRange_NoHeaderPlus1 = Range(Col.Cells(2), Col(Cells(Rows.Count).End(xlUp).Offset(1))
End Function.
Usage in VBA
Set MyRange = IndexSht.GetRange_NoHeaderPlus1("Period")
Public Function RangePeriod() As Object
Dim Col As Range
Set Col = Range(1:1).Find("Period").EntireColumn
Set RangePeriod = Range(Col.Cells(2), Col(Cells(Rows.Count).End(xlUp))
End Function.
Usage in VBA
IndexSht.Range("J1").Copy IndexSht.RangePeriod.Cells(IndexSht.RangePeriod.Cells.Count + 1)
Set MyRange = IndexSht.RangePeriod
One can provide even more protection than Protect Sheet with
Sheet Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target.Rows(1), Rows(1)) is Nothing Then CheckHeaders
End Sub
Private Sub CheckHeaders()
MyHeaders = "Header1, Header2, Header3"
For Each Cel In Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft))
If Not InStr(MyHeaders, Cel) > 0 Then
Application.EnableEvents = False
UndoHeaders
Application.EnableEvents = True
Exit For
Next
End Sub
Private Sub UndoHeaders()
Application.Undo
End Sub
Note that that Combinations of Name and undo code "Should" allow the User to Move columns without breaking the Project