pasting formulas with references to other worksheets

[David]

I have formulas on a worksheet that refer to cells in other worksheets
in the same spreadsheet. I would like to copy the whole worksheet and
paste it into a different spreadsheet. However, when I do the paste,
not only the name of the worksheet is pasted into the new formulas,
but also the name of the old spreadsheet. For example, if worksheets
are called Jan and Feb and I paste in a summary page, I want to add
the figures from the Jan worksheet and the Feb worksheet -- not the
figures on the spreadsheet I am copying the worksheet from.

One way to solve the problem is to make the paste then do find and
replace to eliminate the name of the old spreadsheet from all of the
formulas. Is there a neater way?

Thanks for your help,

Dave

 

[Roger Govier]

Hi David

Try right clicking on Sheet tab>Move or Copy>Click Create copy>Select
your new workbook from the dropdown.

Provided the destination WB doesn't have a sheet of the same name (in
which case it would be called sheetname(2) ) then you should find all
your formulae without references to other workbooks.

 

[David]

Perfect, easy solution -- thanks!! I never would have found it.

David

 

[Roger Govier]

Hi David

You're very welcome. Thanks for the feedback.
Glad it sorted your problem.