Update Range In Template From Open File

I have used the directions below (from this site) to add a name to a validated cell and have that name added to a list. The issue I am trying to solve is this, when opening a new file from the template containing the code below the new file needs to have the range to which I'm refering updated to the range that was in the last opened file from the template. I tried by using a macro to copy and paste the range to an external wb that was hidden and then copy and paste to the new file opened from the template but it was a mess.
If anyone can help by posting some example code to steer me in the right direction I would be very grateful

1. Add any list of names or items to the range A1:A10 on any sheet.

2.Now in cell A11 enter this formula and copy it down to say row 20. =IF( COUNTIF($A$1:A10,$D$1),"x",$D$1) Note the relative reference of A10

3.Go to Insert>Name-define and in the Names in workbook: box type:MyName

4.In the Refers to: box enter this formula: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>x"),1)

5.Click Add then Ok.

6. Select Cell D1 and go to Data> Validation, select List from the Allow: box and in the Source: box type: =MyNames Ensure the In-cell dropdown box is checked.

7.Click the Error Alert page tab and uncheck the Show error alert after invalid data is entered box. Now click Ok.

8.Right click on the sheet name tab and select View Code in here paste the code below:


Private Sub Worksheet_Calculate()
On Error Resume Next
Application.EnableEvents = False
Range("MyNames") = Range("MyNames").Value
Application.EnableEvents = True
On Error Goto 0
End Sub

9.Click the top right X to get back to Excel and now Save.

Now select cell D1 and type in any name, that is NOT part of the list, and Enter. Select D1 again and look at the list. The new name should now be part of it!