Copy & SpecialPaste same cell

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

Robert Christie

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.
 
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 **
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 **
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.
Click to expand...


.
Click to expand...
 
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 said:
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 **
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.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top