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"
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"
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.

EditReplace

what: =

with: zzz

Replace all.

Copy the sheet to other workbook.

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

Reverse the EditReplace steps.


Gord

On Mon, 21 Mar 2005 05:37:02 -0800, "Anne"
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"
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"
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.