Copying worksheets

[Mick]

Hi

When copying a worksheet from one spreadsheet another spreadsheet, the
formulas maintain the link to the data in the original spreadsheet, is there
a way of copying the worksheet so that it does not reference the original
spreadsheet.

Many thanks
Mick

 

[Max]

Try it in this way ..

First, ensure that all dependent sheetnames referenced by the formulas
within the sheet(s) to be copied over actually *exist* within the
destination book before proceeding ..

In the source book,
(Do this one sheet at a time)

Select the entire sheet to be copied
Click Edit > Replace
Find what: =
Replace with: zzzzz
Click Replace All
(this effectively "neutralizes" all formulas on the sheet)

Then copy n paste the sheet over to the destination book,
and reverse the process ..

Select the entire sheet (which is copied over)
Click Edit > Replace
Find what: zzzzz
Replace with: =
Click Replace All
(Array formulas, if any, would need to be re-entered using CSE & re-filled)

Repeat process for next sheet ...

 

[Mick]

Thanks Max.
Just some clarification please, you say

Click Edit > Replace
Find what: =

What am I going to find to replace with zzzz?

If I am doing this from the source book all my formulas are fine, am I
looking to replace my formulas with zzzz?

The worksheet I have is made up of graphs, data and tables being produced
automatically from formulas reading from another two worksheets within the
source workbook, where data is inputted.

A new work book has been created and I wanted to copy over the 'reporting'
spreadsheet.

Thanks again
Mick

 

[David Biddulph]

Thanks Max.
Just some clarification please, you say

What am I going to find to replace with zzzz?

=

 

[Mick]

Got it, thanks, sorry wasn't with it.
Just the arrays that need sorting, many thanks, a good way to do it, saved a
lot of time.
Thanks
Mick

 

[Max]

Glad you got it away, Mick.
David, thanks for the clarification

---