Clear values

[Guest]

Hi
I would like to copy a range and paste it elswhere but only formulas ad
formats, without pasting the values that were int he copied range. How would
I do this?
Thanks!
Rene'

 

[Bob Phillips]

rng1.Copy
rng2.Pastespecial Paste:= xlPasteFormulas
rng2.Pastespecial Paste:= xlPasteFormats

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the reply

My code:
Range("D1376").Select
Selection.Copy
Range("C1:C376").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

This is copying cell contents over to the new range. Also I forgot to ask
the original question of clearing values. If I select a range, can I clear
only the values and not the formulas?
THanks for your help!
Renee

 

[Bob Phillips]

Simplify to

Range("D1376").Copy
With Range("C1:C376").
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

To clear just values, not formulas, you have to test it

Dim cell As Range

For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = ""
End If
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob
It still is copying the values. I just added the clear bit to it and its
working.
Thanks again!