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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

I added this code to my VBA code and it worded a treat:

Please Login or Register  to view this content.

I hope that helps anyone else with the same problem.