Copy sheet into diff. workbooks, update formulas for new workbooks?

[Gingertrees]

In a moment of poor planning, I published a workbook (set up like a form, for people to fill out) without having a page for me that compiles all the fields nicely for easy collation. So the form includes on sheet1:

Name: [--------------------]...Date: [------------]
Address: [--------- ------------------------------]
City: [---------------]...State: [---]...Zip: [------]
And more: [---------------]
(etc - it's long, that's just the start)

I've put together a sheet to pull all those fields together, combo of cell refs and Named Range references:
Name-------- | Date--------- | Address---------- | etc
=Custname |=Sheet1!E2 |=CustAddr

But I know if I copy that sheet to a new workbook it will refer to the old workbook. Any way to copy sheet, maintaining formulas but updating them to the new workbook?

(Using Office 2010)

 

[pbornemeier]

Put all copies of the workbook in a single directory (presumeably they have the person's name or some unique number in the title. Write code to open each file and create new named ranges for it. This should work. You will have to edit/add named ranges"

Option Explicit

Sub CreateNamedRangesInMultipleFiles()

    Dim sFileName As String
    
    sFileName = Dir(ThisWorkbook.Path & "\" & "*.xlsx")
    Do While sFileName <> ""
        Select Case sFileName
        Case ThisWorkbook.Name
            'Do Nothing
        Case Else
            Workbooks.Open Filename:=ThisWorkbook.Path & "\" & sFileName
            ActiveWorkbook.Names.Add Name:="CustName", RefersToR1C1:="=Sheet1!R2C5"
            'More ranges
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        End Select
        sFileName = Dir
    Loop

End Sub