|
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' |
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' |
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' |
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' |
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' |
| All times are GMT +1. The time now is 08:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com