How can I paste formulas only an not values

[Dave Peterson]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

Try

dim targetcell as range
dim sourcecell as range

targetcell.formula = sourcecell.formula

by passes the clipboard and easier to read

 

[David McRitchie]

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