Excel adding filename to formula when creating duplicate file copy

J_R_BRadley 

When creating a duplicate of an Excel file, it's common for Excel to sometimes create references to the original file, especially when the workbook has multiple interlinked worksheets. This can be particularly troublesome when these references should remain internal to the new file. Here are a few strategies to avoid or fix this issue:

Strategy 1: Disable Automatic External Reference Updates

One way to prevent Excel from converting internal references to external ones is to disable the automatic updating of external references before you make the copy:

1. Go to File > Options.
2. Select Advanced.
3. Scroll down to the General section.
4. Uncheck the option Update links to other documents.

Strategy 2: Copy Sheets within Excel

Another approach is to copy sheets within the same workbook and then save the workbook as a new file:

1. Open your original workbook.
2. Right-click on any sheet tab.
3. Select Move or Copy.
4. In the To book dropdown, choose (new book) and check the Create a copy checkbox.
5. Save the newly created workbook with the new name.

Strategy 3: Use VBA to Clean Up References

You can use a VBA macro to go through your formulas and replace external references with internal ones.

Here is a VBA script that can help:

Vba Code is untested, backup your file first.

Sub ReplaceExternalLinks()
    Dim ws As Worksheet
    Dim cell As Range
    Dim formula As String
    Dim externalPath As String
    Dim internalReference As String
    
    externalPath = "[c:\documents\Proj_1234-Client_ABC-2024.05]"
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                formula = cell.Formula
                If InStr(formula, externalPath) > 0 Then
                    internalReference = Replace(formula, externalPath, "")
                    cell.Formula = internalReference
                End If
            End If
        Next cell
    Next ws
End Sub

To use this macro:

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert > Module.
3. Copy and paste the above code into the module.
4. Press F5 to run the macro.

This script searches for formulas containing the external file path and replaces them with internal references.

Strategy 4: Manual Copy with Relative References

If the above methods are not feasible, a more manual but sure way is to copy the content as values before creating the duplicate file:

1. Open your original workbook.
2. Copy the entire content of each worksheet.
3. Paste the content as values (Paste Special > Values) into the same positions within the same workbook.
4. Save the workbook with a new name.
5. Restore the formulas from the original workbook.

This approach ensures that all the references are internal when you save the new workbook.

Strategy 5: Order of Copying Sheets

If the references are being converted because the referenced sheet doesn't exist in the new workbook yet, you can ensure that all referenced sheets are copied first:

1. Start by copying the Project_Details sheet (or any other sheet containing references).
2. Then copy the remaining sheets in the order they reference each other.

This can minimize the chance of Excel creating external references due to missing sheets during the copy process.

Conclusion

While there's no single solution that works perfectly for every situation, these strategies can help you prevent and correct the issue of Excel creating external references when duplicating files. Using a combination of these methods should help maintain internal references within your duplicated workbooks. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.