Copying Down VLOOKUP formula in VBA

I have a spreadsheet that uses VLOOKUP to automatically look up values (£:p) in 12 monthly sheets - Nov 16 to Oct 17 for each person on the list - see image VLOOKUP - Table of Values (1). A have a number of separate sheets like the one shown in the image that add up the values for each person for particular events (such as "Past Captains Match", "Subscription", etc). Everything works fine until I use VBA code to add a new name to each sheet. The inserted name does not refer to the values in the 12 monthly sheets because the relevant VLOOKUP formula is not inserted against their name - see image VLOOKUP - Table of Values (2). Here is part of the VBA code that I use to insert a new name:

' Enter a new name
    
    Name = InputBox("Type in a name")
    
    If Name = "" Then
        MsgBox ("You did not enter a name")
        End
    End If
    
'Turn off screen updating during next process
    
    Application.ScreenUpdating = False
        
'Select worksheet to input new name and resort in alphabetical order
  
    Worksheets("Nov 2016").Select
    
    ActiveSheet.Unprotect
    
'Select top name cell (D8), create blank row below with same formatting
     
    Range("D8").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("D9").Activate
    ActiveCell.Value = Name
    
'Sort name list in alphabetical order
    
    Range("D8").End(xlDown).Select
    Selection.Sort Key1:=Range("D8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
    Range("A1").Select
    
    ActiveSheet.Protect
    
    
'Select worksheet to input new name and resort in alphabetical order
  
    Worksheets("Dec 2016").Select
    
    ActiveSheet.Unprotect
    
'Select top name cell (D8), create blank row below with same formatting
     
    Range("D8").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("D9").Activate
    ActiveCell.Value = Name
    
'Sort name list in alphabetical order
    
    Range("D8").End(xlDown).Select
    Selection.Sort Key1:=Range("D8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
    Range("A1").Select
    
    ActiveSheet.Protect

    Worksheets("Dec 2016").Select
    
    ActiveSheet.Unprotect

...... and so on for the other months.

Is there a way of modifying the above VBA code so that the correct VLOOKUP formula is inserted next the the added name?

Thanking you in anticipation.

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.