Formula in copied worksheet contains file path of original worksheet

[jellybean1974]

Hi

I have two workbooks A and B

I regularly copy 80 sheets from workbook B into workbook A

Once copied, the formulas in those copied sheets refer to the filename of the original workbook.

I have a macro that can find and replace the filename where it appears in any formula between []

Sub FindReplaceRef()
Dim ws      As Worksheet
Application.ScreenUpdating = False
    'this selects all the cells in the workbook that contain formulas
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select

For Each ws In ActiveWorkbook.Worksheets
   'this finds all the references to other sheets and removes them
   Selection.Replace What:="[*]", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Next ws
Application.ScreenUpdating = True
End Sub

this was working fine until I "upgraded" to Excel 2013. Now the formulas in the copied sheets refer to the whole file path and the filename of the original workbook

Modifying the find and replace macro to remove the path does not work

Sub FindReplaceRef()
Dim ws      As Worksheet
Application.ScreenUpdating = False
    'this selects all the cells in the workbook that contain formulas
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select

For Each ws In ActiveWorkbook.Worksheets
   'this finds all the references to other sheets and removes them
   Selection.Replace What:="'[*]'", Replacement:="DocInfo", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Next ws
Call replace_CopyrightYear
Application.ScreenUpdating = True
End Sub

Manually deleting the file path and file name from the formula works, so why not in a macro. Please help me

Thanks

J

 

[Chihiro]

A question. How are you copying sheets to destination?

Depending on the method, I'd suggest another way to copy data.

Ex. Using variant array to transfer formula (note that if workbook/sheet structure is different from source to destination, it may give #REF error).

Sub CopyFormula()
Dim ws As Worksheet
Dim dwb As Workbook
Dim lRow As Long
Dim x As Variant
'Using Sheet1, col A as example
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set dwb = Workbooks.Add

lRow = ws.Range("A" & Rows.Count).Row
x = ws.Range("A1:A" & lRow).Formula

dwb.Sheets(1).Range("A1:A" & lRow).Formula = x

End Sub

 

[jellybean1974]

A question. How are you copying sheets to destination?

Depending on the method, I'd suggest another way to copy data.

Ex. Using variant array to transfer formula (note that if workbook/sheet structure is different from source to destination, it may give #REF error).

Sub CopyFormula()
Dim ws As Worksheet
Dim dwb As Workbook
Dim lRow As Long
Dim x As Variant
'Using Sheet1, col A as example
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set dwb = Workbooks.Add

lRow = ws.Range("A" & Rows.Count).Row
x = ws.Range("A1:A" & lRow).Formula

dwb.Sheets(1).Range("A1:A" & lRow).Formula = x

End Sub

Thanks Chihiro

I should have explained that as well as me copying the sheets, there are several other users that will need to copy sheets from their workbook into mine.

The copy method they will have to use is to have both workbooks open an use the "Move or Copy" sheets between workbooks by right clicking on the sheet and it will be different sheets and differently named sheets every time.

I think any solution to this issue will have to be applied once the sheets have been copied.

Thanks again anyway

J

 

[jellybean1974]

Oops

spotted my error in the find and replace. It should be finding anything between single quotes not between single quote square brackets single quote like i had it before.

Selection.Replace What:="'*'"

I have also moved the selection of special cells inside the For Next loop and made it Next ws.Activate as it wasn't moving to the next sheet before aplying the find/replace

Thanks for you help anyway

J

 

[Debaser]

It's probably quicker to edit the link rather than a find/replace on all formulas, FWIW.

 

[Marc L]

Hi !

See also in VBA inner help ChangeLink method …
​