Copying sheets but keep sheet references in formulas intact

[indyman]

In my workbook, I have formulas on Sheet 2 that refences values on Sheet1. When I want to issue updates, my plan is to have an update button that among other things is to copy data from the wbUserCopy to the UpdatedWB. That process looks like this:

            If Not sheetExist(sSheet, wbNewVersion) > 0 Then 'sheetExist is a function
                wbUserCopy.Sheets(sSheet).Copy Before:=UpdatedWB.Sheets("Sheets1")
                If sheetExist(sSheet, UpdatedWB) > 0 Then
                   'log success/failures
                End If

This all works as I want it to except for the formulas mentioned above. The formula carries the originating workbook name with it, thus creating an external link as shown here:
='[UserCopy.xlsm]User Input'!$B$3
I want to keep that as:
='User Input'!$B$3

I can certainly find "[UserCopy.xlsm]" and replace with nothing. But is there a better way, like a different way to copy the sheet?

 

[Zot]

I think you just need to use Edit Link function to change reference.

Let say I have value in Sheet1 and in Sheet2 I have a formula that link to Sheet1.
I copy Sheet1 to UserUpdateWB. Then Copy Sheet2 to UserUpdateWB. The formula in Sheet2 will have the reference to wbUserCopy. By changing reference the to UserUpdateWB, the external link will go away.

Example of my Test code

Sub RelativeCopy()

Dim wbUserCopy As Workbook
Dim UserUpdateWB As Workbook

Set wbUserCopy = ActiveWorkbook
Set UserUpdateWB = Workbooks("UserUpdate.xlsx")

wbUserCopy.Sheets("Sheet1").Cells.Copy UserUpdateWB.Sheets("Sheet1").Range("A1")
wbUserCopy.Sheets("Sheet2").Cells.Copy UserUpdateWB.Sheets("Sheet2").Range("A1")
UserUpdateWB.ChangeLink Name:="UserCopy.xlsm", NewName:="UserUpdate.xlsx", Type:=xlExcelLinks

End Sub

 

[indyman]

I think you just need to use Edit Link function to change reference.

Let say I have value in Sheet1 and in Sheet2 I have a formula that link to Sheet1.
I copy Sheet1 to UserUpdateWB. Then Copy Sheet2 to UserUpdateWB. The formula in Sheet2 will have the reference to wbUserCopy. By changing reference the to UserUpdateWB, the external link will go away.

Example of my Test code

Sub RelativeCopy()

Dim wbUserCopy As Workbook
Dim UserUpdateWB As Workbook

Set wbUserCopy = ActiveWorkbook
Set UserUpdateWB = Workbooks("UserUpdate.xlsx")

wbUserCopy.Sheets("Sheet1").Cells.Copy UserUpdateWB.Sheets("Sheet1").Range("A1")
wbUserCopy.Sheets("Sheet2").Cells.Copy UserUpdateWB.Sheets("Sheet2").Range("A1")
UserUpdateWB.ChangeLink Name:="UserCopy.xlsm", NewName:="UserUpdate.xlsx", Type:=xlExcelLinks

End Sub

This works perfectly. All I had to do was add that last line of code. I knew that if there was a better way to do it, then someone on here would know. And for any other readers, if the sheets are protected even with

UserInterfaceOnly= True

, then you have to unprotect them to update the links. Thanks.

 

[Zot]

Thanks for update