Paste Special

S

Steve C

I select a portion (page) of my worksheet and click
edit>copy (or Ctrl+C). I then move the cursor to the next
row and click edit>paste special and select formulas then
click ok. Any text (labels) and values computed by the
formulas still copy. What am I doing wrong. I thought if
formulas was selected in Paste Special only formulas
pasted or copied. Please advise.
 
F

Frank Kabel

Hi Steve
the option 'Formulas' copies constant values as well. You may try the
following before copying your data
- hit F5
- click 'special'
- choose 'formulas'
 
T

tjtjjtjt

If you need everything to stay in the same column, I'd do a slight variation on what Frank said. I'd copy and paste the whole thing. Then, I'd select all the values in the newly copied row (it's important to establish the range you want to work with, if you do it this way). Finally, I'd hit F5, choose Special and pick Constants. After clicking okay, you can Clear the highlighted cells, which should include all non-formulas in the newly copied row only

t

----- Steve C wrote: ----

I select a portion (page) of my worksheet and click
edit>copy (or Ctrl+C). I then move the cursor to the next
row and click edit>paste special and select formulas then
click ok. Any text (labels) and values computed by the
formulas still copy. What am I doing wrong. I thought if
formulas was selected in Paste Special only formulas
pasted or copied. Please advise.
 
S

Sandy Mann

Frank Kabel said:
Hi Steve
the option 'Formulas' copies constant values as well.
Click to expand...

OK I give in. What then is the point of the Paste Special 'Formulas'?

Regards

Sandy
 
P

Peo Sjoblom

The only way I know of bypassing this is when you select the part to copy,
select the whole range, then do F5, special and select formulas, then paste
special as formulas.
 
T

tjtjjtjt

My understanding of it is that Paste Formula is designed to copy and Paste the information that appears in the Formula Bar when a cell is selected. If this is a Constant, the Constant is copied and Pasted. If it is a Formula, the Formula is copied and Pasted
I keep it straight by thinking of it as Paste Formula Bar (Contents)

tj
 
R

Ragdyer

However Peo, that doesn't even work, if the copied range is interspersed
with data *other then* formulas.

The <F5> <Special> <SelectFormulas> then acts exactly the same as "Select
and Copy Visible Cells Only", where the "PasteSpecial> <Formulas> then
pastes the originally *non-contiguous* cells into a *contiguous* range,
ruining *all* the formulas with relative references!

I would guess, that defeats the original purpose of the entire exercise.
 
T

tjtjjtjt

Keep in mind that doing it this way will eliminate any cells that didn't contain fomulas and make the fomrula cells contiguous in the paste area
Ex
Label Formula Label Formula Label Formul
GoTo Formla
Past
And the Result will b
Formula Formula Formul
with no blank cells in between them

t

----- Peo Sjoblom wrote: ----

The only way I know of bypassing this is when you select the part to copy
select the whole range, then do F5, special and select formulas, then past
special as formulas
 
P

Peo Sjoblom

Right,

wasn't that what the OP asked for? If he wanted blanks, just copy the whole
thing as
he was doing, then after pasted as formulas while still selected press F5,
select constants,
clcik OK and press delete.
 
Top