How can I paste formulas only an not values

  • Thread starter Thread starter Dave Peterson
  • Start date Start date
D

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
 
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
 
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
 
Try

dim targetcell as range
dim sourcecell as range

targetcell.formula = sourcecell.formula

by passes the clipboard and easier to read
 
Back
Top