Copy Worksheet to another Workbook

[Tail Wind]

Hi,

I have two workbooks open and trying to copy a worksheet from one to
another. I am using the Right click on the tab name, Move or Copy, and
selecting copy checkbox. The copy occurs however the formulas refer to the
orginial workbook which is undesired.

In reading this forum, this should not be occuring. What am I doing wrong?

Thanks in advance and ....Best to you in 2008!

 

[Max]

That's the way Excel behaves when you do the copying

You could try it in this manner:
1. Use Edit>Replace to change all equal signs, viz: = to say: zzz in the
entire sheet
2. Copy the sheet over into the new book, then reverse the process there by
using Edit>Replace to change all: zzz back to equal signs: = in the entire
sheet

 

[Gord Dibben]

Excel 2003

When copying a sheet from book1 to book2 using right-click>move or copy>create a
copy, the formulas do not refer back to book1 unless the original formulas
referred to another sheet in book1.

i.e.

=A1+A2 will copy as such with no reference to book1 or a sheet in book1

=Sheet2!A1 will copy as =[book1.xls]Sheet2A1


Gord Dibben MS Excel MVP

 

[Tail Wind]

Thank you Max.

I have found out copying worksheets is not as easy as it sounds. Controls
like "spinners" must not be part of the Replace search routine. They copy to
the new worksheet with the original worksheet as the control cell link. Live
and learn.

I also made the mistake of renaming a worksheet in the destination worksheet
so as to not lose the data if something went wrong during the copy process.
BIG MISTAKE. This forced Excel to rename formulas to the new worksheet name.
Make a file backup of the workbook instead. And I should have known....what
was I thinking!

Have a great year. Thanks from N. IL USA

 

[Max]

Welcome. Trust you got through that bit ok.

---

 

[Tail Wind]

Thank you Gord and Max,

Gord, you are right. That explains why some of the formulas copied without
incident and others (mostly in my case) didn't.

Thank you both for sharing your knowledge on this forum. I greatly
appreciate it.

Excel 2003

When copying a sheet from book1 to book2 using right-click>move or copy>create a
copy, the formulas do not refer back to book1 unless the original formulas
referred to another sheet in book1.

i.e.

=A1+A2 will copy as such with no reference to book1 or a sheet in book1

=Sheet2!A1 will copy as =[book1.xls]Sheet2A1


Gord Dibben MS Excel MVP

Hi,

I have two workbooks open and trying to copy a worksheet from one to
another. I am using the Right click on the tab name, Move or Copy, and
selecting copy checkbox. The copy occurs however the formulas refer to the
orginial workbook which is undesired.

In reading this forum, this should not be occuring. What am I doing wrong?

Thanks in advance and ....Best to you in 2008!