Remove path from copied formulas

[rex759]

Hello,
I’m using the code,

Workbooks(mydatafile).Sheets("team calendar").Copy _
       after:=Workbooks(TEMPFILE).Sheets("Sheet1")

to copy a worksheet to a new workbook. What is happening is the path to the Master workbook is adding itself to each of the formulas.
Example;

=IF(ISERROR(INDEX('C:\Users\Michael''s\Desktop\[Master.xls]Data'!$U$2:$U$2000,MATCH(--'Team Calendar'!A6,'C:\Users\Michael''s\Desktop\[Master.xls]Data'!$F$2:$F$2000,0),0)),"",INDEX('C:\Users\Michael''s\Desktop\[Master.xls]Data'!$U$2:$U$2000,MATCH(--'Team Calendar'!A6,'C:\Users\Michael''s\Desktop\[Master.xls]Data'!$F$2:$F$2000,0),0))

I thought I could run a code to remove the path at the end but didn't have any luck. I was using.

    Dim cell As Range, n As Variant

    For Each cell In Workbooks("tempfile").Sheets("team calendar").Cells.SpecialCells(xlFormulas)
        n = Application.Find("]", cell.Formula)
        If Not IsError(n) Then
            cell.Formula = "='" & Right(cell.Formula, Len(cell.Formula) - n)
        End If
    Next cell

I get an application error on this line.

cell.Formula = "='" & Right(cell.Formula, Len(cell.Formula) - n)

any help is appreciated.

 

[JoeMo]

Untested, but you can try this:

Sub trial()
Const path = "'C:\Users\Michael''s\Desktop\[Master.xls]"
Dim rng As Range
Set rng = Workbooks("tempfile").Sheets("team calendar").Cells.SpecialCells(xlFormulas)
With rng
    .Replace path, ""
End With
    
End Sub

 

[xenou]

You can also go to Edit | Links. Then select the external workbook, and change the link. Select the workbook that you are in (i.e., you want to "link it to itelf")

In 2007 edit links is on the data tab.

If you insert a new sheet first, then copy the cells and paste them in the new sheet, I think it might not create the link (as opposed to copy the entire sheet itself into a new workbook)

 

[rex759]

Thank you both for your replies. I realized after trial an error that I had to change most of the cell references in the formulas that carried over to the new worksheet. This made it too cumbersome to just update the link. I have a few ideas to try. If I get suck again, I’ll post.

Thank you