Copying Down VLOOKUP formula in VBA

I use command buttons linked toVBA code to add and delete names from 12 monthly sheets and 6 event sheets. this cuts down on the effort needed and ensures that all the names are exactly the same (needed for VLOOKUP function). I could simply drag the VLOOKUP formula on each of the relevant worksheets but I wanted to do this automatically when the name is added.

Can this be done using formulas and names ranges as suggested?