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

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
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)
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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"

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top