copying cells with formulas, numbers, & text

[freeriderxlt]

I have Excel 2007 and I am having trouble copying from one worksheet
to another. I am trying to copy some text, numbers and formulas from
one worksheet in a workbook to another worksheet in another workbook
and when it copies, the formulas do not come over, they are pasted as
numbers instead. When I try to paste as formulas, the option is
"grayed-out" and the system only give me the option of pasting as an
object or a link.

 

[Socko]

This happened once with me. I finally found out that the reason I was
not able to copy formulas was because the sheet was protected and the
cell properties was hidden. Check for worksheet protection and cell
protection/hidden properties of all the cells in a sheet.

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, and other resources
Please visit: http://socko.wordpress.com

..

 

[Dave Peterson]

It sounds like you have two instances of excel running.

Close one of those instances.

Then in the first instance, open the second file (ctrl-o). Then try the
copy|paste.

 

[freeriderxlt]

It sounds like you have two instances of excel running.

Close one of those instances.  

Then in the first instance, open the second file (ctrl-o).  Then try the
copy|paste.

I tried what you mentioned but the formula that is pasted now referes
to the workbook sheet that I copied from. I was hoping to just copy
the formulas and have them refer to the same cells in the new
worksheet.

 

[Dave Peterson]

If you only have a few cell formulas to copy, you can:
Select each cell
Select the formula in the formula bar
Copy it
And paste into the formula bar with the receiving cell selected.

If you have lots, then you can do this technique that I use:
Select the range to copy
edit|replace
what: =
with: $$$$$=
replace all

Now all your formulas are simply text.

Do the edit|copy and edit|paste
And reverse the edit|replace (in both the sending worksheet and receiving
worksheet):

Select the range that was pasted
edit|replace
what: $$$$$=
with: =
replace all

Now all the text strings will be formulas.

 

[Ajay]

If you only have a few cell formulas to copy, you can:
Select each cell
Select the formula in the formula bar
Copy it
And paste into the formula bar with the receiving cell selected.

If you have lots, then you can do this technique that I use:
Select the range to copy
edit|replace
what:  =
with:  $$$$$=
replace all

Now all your formulas are simply text.

Do the edit|copy and edit|paste
And reverse the edit|replace (in both the sending worksheet and receiving
worksheet):

Select the range that was pasted
edit|replace
what:  $$$$$=
with:  =
replace all

Now all the text strings will be formulas.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Open both files in same instance using Ctrl + O, then use Special
paste as Formulas.

 

[Dave Peterson]

It sounds like that the formulas that the OP was copying refer to other
worksheets in the sending workbook. And when they're pasted, the pasted
formulas refer to the sheets in the original workbook.

If that's true (and that's what the OP posted in one of the follow ups), then
this won't do what the OP wants.