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.