# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] How can I paste formulas only an not values

## RAD

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

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

----------


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

--
Regards,
Tom Ogilvy






"RAD" <RAD@discussions.microsoft.com> wrote in message
news:A6B43722-9042-4F01-BC27-C7CABBDAAB44@microsoft.com...
> 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

----------


## Vacation's Over

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

----------


## 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
---
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" <RAD@discussions.microsoft.com> wrote in message news:A6B43722-9042-4F01-BC27-C7CABBDAAB44@microsoft.com...
> 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

----------

