Copying sheets to another workbook-without links

[Kinney]

How do I copy a sheet to another workbook without the link to the original and still maintain the formula?

 

[Buckyg66]

Here is what I do:

1. Select all of the cells in your worksheet that you want to move/copy.
2. Do a find and replace on all = and replace with with something that is not in your formulas, such as z or zz
3. Copy worksheet to new worksheet
4. Then in the new worksheet, do a find and replace on the z or zz back to =.
5. All of your formulas are now the same as the original without the links changed.

Hope this helps.

 

[evienlie]

Thanks! Learnt something new, never thought of this..

Here is what I do:

1. Select all of the cells in your worksheet that you want to move/copy.
2. Do a find and replace on all = and replace with with something that is not in your formulas, such as z or zz
3. Copy worksheet to new worksheet
4. Then in the new worksheet, do a find and replace on the z or zz back to =.
5. All of your formulas are now the same as the original without the links changed.

Hope this helps.

 

[Philippos]

Worked for me too. Thanks. Unbelivable what kind of trucs one needs to perform in Excel to accomplish stuff....

Here is what I do:

1. Select all of the cells in your worksheet that you want to move/copy.
2. Do a find and replace on all = and replace with with something that is not in your formulas, such as z or zz
3. Copy worksheet to new worksheet
4. Then in the new worksheet, do a find and replace on the z or zz back to =.
5. All of your formulas are now the same as the original without the links changed.

Hope this helps.

 

[gkid650]

here is what i do:

1. Select all of the cells in your worksheet that you want to move/copy.
2. Do a find and replace on all = and replace with with something that is not in your formulas, such as z or zz
3. Copy worksheet to new worksheet
4. Then in the new worksheet, do a find and replace on the z or zz back to =.
5. All of your formulas are now the same as the original without the links changed.

Hope this helps.

that's brilliant!!! Thank you!

 

[BKChedlia]

Hi All,

I tried using VBA, but even with the replace it update the link.

How do I copy / paste an entire sheet with columns with formulas to one workboot to another without the source link ?

Example.

Formula VLOOKUP(A2,test!A:B,2,false) to source file (a.xls)
become VLOOKUP(A2,[a.xls]test!A:B,2,false) to destination (b.xls)

Thank you

 

[HolliIsAwesome]

oh my lanta Buckyg...you are my HERO!!!!
Seriously...
I work in the clinical mental health specialty, and work heaving in Excel and this has been a major major problem for me. I am totally try this RIGHT now!.. I pray it works....oh please let it work... let it copy and not link back to the workbook I copied it from...

 

[csmith0320]

Fantastic solution, Buckygg! Much appreciated!