How can I paste formulas only an not values

In a script I want to copy only formulas, not values.

Currently I use this code for the 'paste' operation, but this also copy raw
values, what I do not want.

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Thanks

So your selection is a combination of constants and formulas?

If yes, copy the range, paste the formulas and then clear the constants from
that pasted range.

Something like:

dim rng as range
dim destcell as range

set rng = selection
set destcell = worksheets("sheet1").range("a1")

rng.copy
destcell.pastespecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

on error resume next
destcell.resize(rng.rows.count,rng.columns.count) _
.cells.specialcells(xlcelltypeconstants).clearcontents
on error goto 0



RAD wrote:
>
> In a script I want to copy only formulas, not values.
>
> Currently I use this code for the 'paste' operation, but this also copy raw
> values, what I do not want.
>
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
>
> Thanks

--

Dave Peterson

Raw values are considered formulas for the purpose of pasting. So you would
have to avoid copying cells that contain constants.


selection.Specialcells(xlformulas).copy

will just copy cells with formulas - however, it will skip the cells it
doesn't copy and this may not be what you want.

you could paste the original selection as you are doing, then clear out the
constants

set rng = selection
rng.copy Range("Z100")
Range("Z100").Resize(rng.rows.count, _
rng.columns.count).specialcells(xlconstants).clear

--
Regards,
Tom Ogilvy






"RAD" <[email protected]> wrote in message
news:[email protected]...
> In a script I want to copy only formulas, not values.
>
> Currently I use this code for the 'paste' operation, but this also copy
raw
> values, what I do not want.
>
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
>
> Thanks

Try

dim targetcell as range
dim sourcecell as range

targetcell.formula = sourcecell.formula

by passes the clipboard and easier to read

"RAD" wrote:

> In a script I want to copy only formulas, not values.
>
> Currently I use this code for the 'paste' operation, but this also copy raw
> values, what I do not want.
>
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
>
> Thanks

From the other answers and since they all seem to lead in about
the same direction, it might be possible that you are trying to do something
like the first macro, InsertRowsAndFillFormulas in
http://www.mvps.org/dmcritchie/excel/insrtrows.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"RAD" <[email protected]> wrote in message news:[email protected]...
> In a script I want to copy only formulas, not values.
>
> Currently I use this code for the 'paste' operation, but this also copy raw
> values, what I do not want.
>
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
>
> Thanks