Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot copy formulas only
I use this line in my macro
Range("b2:z2").Select Application.CutCopyMode = False Selection.Copy Range(doel).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Some how excel copies also the values. What am i doing wrong? Greetings Hans |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot copy formulas only
Hi Hans,
You aren't doing anything wrong, this is how PasteSpecial/Formulas works. If the copied cell contains a formula, the formula is pasted. If the copied cell contains a value, the value is pasted. What you can do is delete the values after you paste special. Something like this: Range("b2:z2").Copy Range(doel).PasteSpecial Paste:=xlPasteFormulas On Error Resume Next Range(doel).Resize(1, 25) _ .SpecialCells(xlCellTypeConstants, 23).ClearContents On Error GoTo 0 The On Error Resume Next/On Error GoTo 0 around the SpecialCells method is required because it will error if you copy and paste a range that contains only formulas. If you know there will always be values in the copied range you can remove these two lines. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "hans" wrote in message ... I use this line in my macro Range("b2:z2").Select Application.CutCopyMode = False Selection.Copy Range(doel).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Some how excel copies also the values. What am i doing wrong? Greetings Hans |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot copy formulas only
Thank for the info
Greetings Hans "Rob Bovey" schreef in bericht ... Hi Hans, You aren't doing anything wrong, this is how PasteSpecial/Formulas works. If the copied cell contains a formula, the formula is pasted. If the copied cell contains a value, the value is pasted. What you can do is delete the values after you paste special. Something like this: Range("b2:z2").Copy Range(doel).PasteSpecial Paste:=xlPasteFormulas On Error Resume Next Range(doel).Resize(1, 25) _ .SpecialCells(xlCellTypeConstants, 23).ClearContents On Error GoTo 0 The On Error Resume Next/On Error GoTo 0 around the SpecialCells method is required because it will error if you copy and paste a range that contains only formulas. If you know there will always be values in the copied range you can remove these two lines. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "hans" wrote in message ... I use this line in my macro Range("b2:z2").Select Application.CutCopyMode = False Selection.Copy Range(doel).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Some how excel copies also the values. What am i doing wrong? Greetings Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Formulas | Excel Discussion (Misc queries) | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
How do I copy formulas but using the same range each time I copy | Excel Worksheet Functions | |||
Copy Value not formulas | Excel Worksheet Functions | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |