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

[Anne]

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.

 

[Gord Dibben]

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

 

[Anne]

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]

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

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)

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

 

[Anne]

Thanks Gord, that does work!

 

[Gord Dibben]

Thanks for the feedback Anne.


Gord

Thanks Gord, that does work!