I got no response to my last request for advice and so I spent time trying to work it out for myself.
I wanted to ensure that the correct VLOOPUP formula was placed next to the added name. The above code leaves
the cell blank. It was possible to do this manually by dragging down the formula from the cell above and so
I used the macro recorder to see what happened to the underlying VBA code. This is what I found:

' Copy down formula from previous row cell

    Range("C6").Select
    Selection.AutoFill Destination:=Range("C6:C7"), Type:=xlFillDefault
    Range("C6:C7").Select
I added this code to my VBA code and it worded a treat:

Worksheets("Xmas Lunch").Select
    
    ActiveSheet.Unprotect
    
'Select top name cell (B6), create blank row below with same formatting
     
    Range("B6").Activate
    ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
    ActiveCell.EntireRow.Copy
    ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
    
'Select blank name cell and insert new name
        
    Range("B7").Activate
    ActiveCell.Value = Name
    
' Copy down formula from previous row cell
    
    Range("C6").Select
    Selection.AutoFill Destination:=Range("C6:C7"), Type:=xlFillDefault
    Range("C6:C7").Select
    
'Sort name list in alphabetical order
    
    Range("B6").End(xlDown).Select
    Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
    Range("A1").Select
    
    ActiveSheet.Protect
I hope that helps anyone else with the same problem.