Copy without original file references.

[Mridul Gupta]

How do I copy a sheet with formulas from one file to another without original file references in formulas?

for example: =Invoice!B5 is copied as =[original-file-name.xlsx]!Invoice!B5

I want only =Invoice!B5

Regards

 

[sgmpatnaik]

@Mridul

Welcome to Chandoo.org, Glad you Are here

you can use move or copy option

open your both files

1. Select the Invoice!b5 sheet
2. Press the mouse right click button
3. Select Move or copy
4. Select the Option To Book (where you want to move the sheet)
5. Choose where you want to past the sheet in Before Sheet
6. Select and press the Create a Copy
7. Ok

Now the Invoice sheet is copied to another book

Hope it is clear you problem

Thanks

 

[NARAYANK991]

Hi Mridul ,

In case your problem is still unresolved , check the suggestions made in this link :

http://superuser.com/questions/3974...-maintain-relative-cell-reference-in-formulas

Narayan

 

[Smallman]

Hi

I cracked this puppy a while back here is the link;

http://www.ozgrid.com/forum/showthread.php?t=168864

Post 7 - however it looks like it only solves for 1 cell. Here is the code adapted for a range of cells.

Sub RemLink()
    Dim rng As Range
    Dim r As Range
    Dim i As Variant
    Dim j As Variant
    Dim st As String
    Dim str As String
    Dim strJn As String
   
    Set rng = [A3:D8]
   
    For Each r In rng
        j = Application.Find("[", r.Formula)
        i = Application.Find("]", r.Formula)
        st = Left(r.Formula, j - 1)
        str = Right(r.Formula, Len(r.Formula) - i)
        strJn = st & str
        r.Value = strJn
    Next
   
End Sub

Could probably shrink it a bit but it works OK from my initial tests.

Take care

Smallman

 

[Xiq]

What you also can do this the following:

1. Press ctrl + h
2. Type the equal sign ( = ) in the "Find what"-box
3. Type a unique bit of string in the "Replace with"-box. E.g.: &&&
4. Make sure the "Look in"-box has "Formulas" selected.
5. Press "Replace All"
6. Now you can copy the formulas (which are now broken for now).
7. To repair the formulas, repeat step 1, 3, 2, 4 and 5 in this order.
8. ...
9. Profit!