Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & SpecialPaste same cell
Hi
I want to copy from and SpecialPaste values only, into the same cell. I recorded the following code but can't seem to remove the 'Select' from code. Range("F4").FormulaR1C1 = "=RIGHT(RC[-5],6)" Range("F4").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I tried Copy Destination but receive error 424 object required. Range("F4").Copy Destination = Range"F4") _ ..PasteSpecial.xlValues What object is required? TIA Bob C. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & SpecialPaste same cell
Robert, how about this?
Range("F4") = Range("F4").Value -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Robert Christie" wrote in message ... Hi I want to copy from and SpecialPaste values only, into the same cell. I recorded the following code but can't seem to remove the 'Select' from code. Range("F4").FormulaR1C1 = "=RIGHT(RC[-5],6)" Range("F4").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I tried Copy Destination but receive error 424 object required. Range("F4").Copy Destination = Range"F4") _ .PasteSpecial.xlValues What object is required? TIA Bob C. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & SpecialPaste same cell
This still leaves the formats. If you want to get rid of this then also use
range("F4").ClearFormats -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Paul B" wrote in message ... Robert, how about this? Range("F4") = Range("F4").Value -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Robert Christie" wrote in message ... Hi I want to copy from and SpecialPaste values only, into the same cell. I recorded the following code but can't seem to remove the 'Select' from code. Range("F4").FormulaR1C1 = "=RIGHT(RC[-5],6)" Range("F4").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I tried Copy Destination but receive error 424 object required. Range("F4").Copy Destination = Range"F4") _ .PasteSpecial.xlValues What object is required? TIA Bob C. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & SpecialPaste same cell
Paul
That is so simple. Thankyou. One question has anyone written a book or web site showing the conversion from recorded macros to refined faster code.? I searched this group and tried a couple lines of code I thought might do the job, but didn't succeed in finding the solution. Thanks again Regards Bob C. -----Original Message----- Robert, how about this? Range("F4") = Range("F4").Value -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Robert Christie" wrote in message ... Hi I want to copy from and SpecialPaste values only, into the same cell. I recorded the following code but can't seem to remove the 'Select' from code. Range("F4").FormulaR1C1 = "=RIGHT(RC[-5],6)" Range("F4").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I tried Copy Destination but receive error 424 object required. Range("F4").Copy Destination = Range"F4") _ .PasteSpecial.xlValues What object is required? TIA Bob C. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & SpecialPaste same cell
almost all books on Excel VBA programming start with recorded code and
quickly move to writing "normal" code. Commonly recommended books are John Walkenbach's power programming (http://www.j-walk.com/ss/excel) John Green's and Stephen Bullen's (latest version with Bovey and Rosenburg) book http://www.bmsltd.ie (published by Wrox press, which is now out of business). -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Paul That is so simple. Thankyou. One question has anyone written a book or web site showing the conversion from recorded macros to refined faster code.? I searched this group and tried a couple lines of code I thought might do the job, but didn't succeed in finding the solution. Thanks again Regards Bob C. -----Original Message----- Robert, how about this? Range("F4") = Range("F4").Value -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Robert Christie" wrote in message ... Hi I want to copy from and SpecialPaste values only, into the same cell. I recorded the following code but can't seem to remove the 'Select' from code. Range("F4").FormulaR1C1 = "=RIGHT(RC[-5],6)" Range("F4").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I tried Copy Destination but receive error 424 object required. Range("F4").Copy Destination = Range"F4") _ .PasteSpecial.xlValues What object is required? TIA Bob C. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other? | Excel Programming |