| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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' |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear values
rng1.Copy
rng2.Pastespecial Paste:= xlPasteFormulas rng2.Pastespecial Paste:= xlPasteFormats -- HTH RP (remove nothere from the email address if mailing direct) "Rene'" wrote in message ... 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' |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear values
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'" wrote in message ... 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' |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear values
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" wrote in message ... 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'" wrote in message ... 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' |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear values
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" wrote in message ... 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'" wrote in message ... 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' |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with macro to copy, paste, and clear cell values | Excel Discussion (Misc queries) | |||
| How to clear the lowest 10 values from a list of 30 values | New Users to Excel | |||
| Clear values but keep formulas? | Excel Discussion (Misc queries) | |||
| Automatically clear values from a range of selected cells | Excel Discussion (Misc queries) | |||
| How do I clear a worksheet of values, but leave formulas intact? | Excel Worksheet Functions | |||
Linear Mode
