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.

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.

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.

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.


.

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.


.