how to copy an excel sheet from one xls to another without links

I want to use the move/copy worksheet function to copy a worksheet from one
workbook to another. When I do this I want it to Not refer to the workbook it
was copied from in the formulas and lookups. I have tried copy/paste special
also and it doesn't work.

Anne

Save workbook1 and leave open.

Open workbook2.

With workbook1 active, right-click on the sheet tab you want to send to
workbook2.

Select "Move or Copy" and select workbook2 from "to book". Do not select
"create a copy".

Hit OK and it's done.

Go back to workbook1 and close without saving.

Now do whatever you want with workbook2.


Gord Dibben Excel MVP

On Thu, 17 Mar 2005 10:29:03 -0800, "Anne" <[email protected]>
wrote:

>I want to use the move/copy worksheet function to copy a worksheet from one
>workbook to another. When I do this I want it to Not refer to the workbook it
>was copied from in the formulas and lookups. I have tried copy/paste special
>also and it doesn't work.

I tried this but it still refers to the second workwook in the formula for a
lookup.
It copies as: =VLOOKUP(A11,'N:\Blank Design
Sheets\[BlankWorkBook03-11-05.xls]SRNL'!$A$2:$B$265,2)

When I want it to copy: =VLOOKUP(A11,SRNL!$A$2:$B$265,2)

"Gord Dibben" wrote:

> Anne
>
> Save workbook1 and leave open.
>
> Open workbook2.
>
> With workbook1 active, right-click on the sheet tab you want to send to
> workbook2.
>
> Select "Move or Copy" and select workbook2 from "to book". Do not select
> "create a copy".
>
> Hit OK and it's done.
>
> Go back to workbook1 and close without saving.
>
> Now do whatever you want with workbook2.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 17 Mar 2005 10:29:03 -0800, "Anne" <[email protected]>
> wrote:
>
> >I want to use the move/copy worksheet function to copy a worksheet from one
> >workbook to another. When I do this I want it to Not refer to the workbook it
> >was copied from in the formulas and lookups. I have tried copy/paste special
> >also and it doesn't work.
>
>

Anne

That is because you are referring to a sheetname in your original formula.

My suggestion did not take that into account.

Try this one...

In worksheet to be copied hit CTRL + A(twice in 2003) to select all cells.

Edit>Replace

what: =

with: zzz

Replace all.

Copy the sheet to other workbook.

Note: other workbook must have also have the sheet named SRNL.

Reverse the Edit>Replace steps.


Gord

On Mon, 21 Mar 2005 05:37:02 -0800, "Anne" <[email protected]>
wrote:

>I tried this but it still refers to the second workwook in the formula for a
>lookup.
>It copies as: =VLOOKUP(A11,'N:\Blank Design
>Sheets\[BlankWorkBook03-11-05.xls]SRNL'!$A$2:$B$265,2)
>
>When I want it to copy: =VLOOKUP(A11,SRNL!$A$2:$B$265,2)
>
>"Gord Dibben" wrote:
>
>> Anne
>>
>> Save workbook1 and leave open.
>>
>> Open workbook2.
>>
>> With workbook1 active, right-click on the sheet tab you want to send to
>> workbook2.
>>
>> Select "Move or Copy" and select workbook2 from "to book". Do not select
>> "create a copy".
>>
>> Hit OK and it's done.
>>
>> Go back to workbook1 and close without saving.
>>
>> Now do whatever you want with workbook2.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Thu, 17 Mar 2005 10:29:03 -0800, "Anne" <[email protected]>
>> wrote:
>>
>> >I want to use the move/copy worksheet function to copy a worksheet from one
>> >workbook to another. When I do this I want it to Not refer to the workbook it
>> >was copied from in the formulas and lookups. I have tried copy/paste special
>> >also and it doesn't work.
>>
>>

Thanks Gord, that does work!

"Anne" wrote:

> I want to use the move/copy worksheet function to copy a worksheet from one
> workbook to another. When I do this I want it to Not refer to the workbook it
> was copied from in the formulas and lookups. I have tried copy/paste special
> also and it doesn't work.

Thanks for the feedback Anne.


Gord

On Mon, 21 Mar 2005 10:45:07 -0800, "Anne" <[email protected]>
wrote:

>Thanks Gord, that does work!
>
>"Anne" wrote:
>
>> I want to use the move/copy worksheet function to copy a worksheet from one
>> workbook to another. When I do this I want it to Not refer to the workbook it
>> was copied from in the formulas and lookups. I have tried copy/paste special
>> also and it doesn't work.

I know this is an old question/post but, here's a really easy way.

1 - With workbook 1 open, open workbook 2.
2 - Right-click on the worksheet in workbook 2 you wish to copy to workbook 1, and select Move or Copy.
3 - Select workbook 1 from the pulldown at the top of the window and put the sheet in the order you want it (if you want to check the box to Create a Copy so be it...else you will move that sheet from 2 to 1 so be careful!).
4 - Once the copy is created, then in workbook 1, select all the cells on the just copied sheet.
5 - On the menu bar go to Data -> Edit Links. Change the file location and name to workbook 1 and voila! All of the formulas will remain in tact but without the path/filename of workbook 2 anymore.

Notes: I did this on Office 2010. I needed to do this because I messed up workbook 1 while editing it and wanted to get back all of the formulas without re-creating everything. I had a backup of the original located elsewhere before I started, so the two had the same sheetnames otherwise.