Clear values

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'

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

--

HTH

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


"Rene'" <Rene'@discussions.microsoft.com> wrote in message
news:34A05B25-3F4F-4F4D-9A86-D8D73B041A70@microsoft.com...
> 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'

Thanks for the reply

My code:
Range("D1:D376").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" wrote:

> rng1.Copy
> rng2.Pastespecial Paste:= xlPasteFormulas
> rng2.Pastespecial Paste:= xlPasteFormats
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Rene'" <Rene'@discussions.microsoft.com> wrote in message
> news:34A05B25-3F4F-4F4D-9A86-D8D73B041A70@microsoft.com...
> > 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'
>
>
>

Simplify to

Range("D1:D376").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)


"Renee" <Renee@discussions.microsoft.com> wrote in message
news:EA78FD9D-D461-4B0E-865B-79B6FFD8DEE4@microsoft.com...
> Thanks for the reply
>
> My code:
> Range("D1:D376").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" wrote:
>
> > rng1.Copy
> > rng2.Pastespecial Paste:= xlPasteFormulas
> > rng2.Pastespecial Paste:= xlPasteFormats
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Rene'" <Rene'@discussions.microsoft.com> wrote in message
> > news:34A05B25-3F4F-4F4D-9A86-D8D73B041A70@microsoft.com...
> > > 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'
> >
> >
> >

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

"Bob Phillips" wrote:

> Simplify to
>
> Range("D1:D376").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)
>
>
> "Renee" <Renee@discussions.microsoft.com> wrote in message
> news:EA78FD9D-D461-4B0E-865B-79B6FFD8DEE4@microsoft.com...
> > Thanks for the reply
> >
> > My code:
> > Range("D1:D376").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" wrote:
> >
> > > rng1.Copy
> > > rng2.Pastespecial Paste:= xlPasteFormulas
> > > rng2.Pastespecial Paste:= xlPasteFormats
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Rene'" <Rene'@discussions.microsoft.com> wrote in message
> > > news:34A05B25-3F4F-4F4D-9A86-D8D73B041A70@microsoft.com...
> > > > 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'
> > >
> > >
> > >
>
>
>