VBA code to change sheet reference in formula

[nina212001]

I'm not the most proficient with VBA but have been doing a lot of googling to create one. What I've created is VBA code to insert and prompt a title for the new worksheet. I have formulas on a specific worksheet linked to various other worksheets and I need my new row (copy/inserted by the macro already) to change the sheet reference to the new worksheet. Each time the worksheet name will vary because the macro is taking the last row of data and copying/inserting new row after. Formula reference below:
=IF((VLOOKUP(C$3,'Club 6.24.20'!$A:$B,2,FALSE))="","",(VLOOKUP(C$3,'Club 6.24.20'!$A:$B,2,FALSE)))

How do I set the VBA to recognize the old worksheet name and insert the new worksheet name?

 

[nina212001]

I included a screenshot so hopefully to help. If I run the previous code, it will take row 12 and insert a copied row below. Meaning row 13 will become the last row for the step to replace sheet reference in formulas. I am using colums A to AO but might have a need to insert additional columns, which was why I hoped to user the entire row as the range. There will not be data below.