I thought the intention behind Paste Special - Fomulas was to copy across
only the contents of cells that contain a fomula (i.e. begins with an '='
sign) and NOT those that contain a value. Have I got this wrong? It certainly
doesn't work that way for me since both formulas and values appear in the
paste area.

No, that is not the intention. What you require can be achieved via EditGo
toSpecial, check Formulas and then copy and paste.

--
Kind regards,

Niek Otten

"bjw10" wrote in message
...
I thought the intention behind Paste Special - Fomulas was to copy across
only the contents of cells that contain a fomula (i.e. begins with an '='
sign) and NOT those that contain a value. Have I got this wrong? It
certainly
doesn't work that way for me since both formulas and values appear in the
paste area.

Niek,
Many thanks, great idea. I had no idea what the GoTo function did until now.
I will definitely use it in future.

However, it doesn't quite solve my current problem in that my formulas are
spread out over multiple places in the worksheet and following the GoTo you
can't copy them all together in a single copy command. If I copy/paste them
individually it's almost the same as having to manually update each
individual formula in the target area.

You seem very definite about the function of Paste Special and yet the doc
seems clear that clicking the formula button only "pastes formulas as entered
in the formula bar" and elsewhere formulas are defined as expressions
starting with an '='. Yet this is not how it works in practice.

Could this be a bug and not a feature?

Thanks anyway for your suggestion.

Best regards,
Brian.

"Niek Otten" wrote:

No, that is not the intention. What you require can be achieved via EditGo
toSpecial, check Formulas and then copy and paste.

--
Kind regards,

Niek Otten

"bjw10" wrote in message
...
I thought the intention behind Paste Special - Fomulas was to copy across
only the contents of cells that contain a fomula (i.e. begins with an '='
sign) and NOT those that contain a value. Have I got this wrong? It
certainly
doesn't work that way for me since both formulas and values appear in the
paste area.

You're right, you can't copy it in one command. Sorry for that. But you can
clear multiple selections, which leads me to this alternative:

Copy the whole range. GotoSpecialConstants, EditClear all.

Be careful with your original constants!

Not all definitions in Excel are quite accurate. Sometimes a number is
considered a formula, sometimes it isn't.
You see the numbers in the formula bar too.
I think "formulas" is used here as opposed to "values", meaning results of
formulas.

--
Kind regards,

Niek Otten


"bjw10" wrote in message
...
Niek,
Many thanks, great idea. I had no idea what the GoTo function did until
now.
I will definitely use it in future.

However, it doesn't quite solve my current problem in that my formulas are
spread out over multiple places in the worksheet and following the GoTo
you
can't copy them all together in a single copy command. If I copy/paste
them
individually it's almost the same as having to manually update each
individual formula in the target area.

You seem very definite about the function of Paste Special and yet the doc
seems clear that clicking the formula button only "pastes formulas as
entered
in the formula bar" and elsewhere formulas are defined as expressions
starting with an '='. Yet this is not how it works in practice.

Could this be a bug and not a feature?

Thanks anyway for your suggestion.

Best regards,
Brian.

"Niek Otten" wrote:

No, that is not the intention. What you require can be achieved via
EditGo
toSpecial, check Formulas and then copy and paste.

--
Kind regards,

Niek Otten

"bjw10" wrote in message
...
I thought the intention behind Paste Special - Fomulas was to copy
across
only the contents of cells that contain a fomula (i.e. begins with an
'='
sign) and NOT those that contain a value. Have I got this wrong? It
certainly
doesn't work that way for me since both formulas and values appear in
the
paste area.

Thanks again Nick, really appreciate your efforts, but this doesn't quite
crack it either.

Think I did as you suggested and having highlighted and then deleted the
constants tried to copy the residual formulas to the target area with "skip
blanks" specified. This, too, did not work as I expected, since the blank
areas from the copy area overwrote fields in the paste area, which I wanted
to retain.

Maybe I should just accept I've gotta do it longhand.

Cheers,
Brian.

"Niek Otten" wrote:

You're right, you can't copy it in one command. Sorry for that. But you can
clear multiple selections, which leads me to this alternative:

Copy the whole range. GotoSpecialConstants, EditClear all.

Be careful with your original constants!

Not all definitions in Excel are quite accurate. Sometimes a number is
considered a formula, sometimes it isn't.
You see the numbers in the formula bar too.
I think "formulas" is used here as opposed to "values", meaning results of
formulas.

--
Kind regards,

Niek Otten


"bjw10" wrote in message
...
Niek,
Many thanks, great idea. I had no idea what the GoTo function did until
now.
I will definitely use it in future.

However, it doesn't quite solve my current problem in that my formulas are
spread out over multiple places in the worksheet and following the GoTo
you
can't copy them all together in a single copy command. If I copy/paste
them
individually it's almost the same as having to manually update each
individual formula in the target area.

You seem very definite about the function of Paste Special and yet the doc
seems clear that clicking the formula button only "pastes formulas as
entered
in the formula bar" and elsewhere formulas are defined as expressions
starting with an '='. Yet this is not how it works in practice.

Could this be a bug and not a feature?

Thanks anyway for your suggestion.

Best regards,
Brian.

"Niek Otten" wrote:

No, that is not the intention. What you require can be achieved via
EditGo
toSpecial, check Formulas and then copy and paste.

--
Kind regards,

Niek Otten

"bjw10" wrote in message
...
I thought the intention behind Paste Special - Fomulas was to copy
across
only the contents of cells that contain a fomula (i.e. begins with an
'='
sign) and NOT those that contain a value. Have I got this wrong? It
certainly
doesn't work that way for me since both formulas and values appear in
the
paste area.

Brian,

Could this UDF be of help?

Function CellHasFormula(c as Range)
If Left(c.FormulaR1C1,1) = "=" Then
CellHasFormula = True
Else
CellHasFormula = False
Endif
End Function

If your sheet layout allows it, you can populate helper columns with
=CellHasFormula(A1)
and then filter on True/False.

HTH
Kostis Vezerides