A More Effective Way of Using PasteSpecial?

  • Thread starter Thread starter CalumMurdo Kennedy
  • Start date Start date
C

CalumMurdo Kennedy

Hi,

I am trying to copy and (Special) paste and am trying to work out if there
is a better way of doing this. I understand that if you want to copy and
paste a cell then it is faster to specify the destination in the same line
and avoid actually selecting the cells at all (like this):

Range("A1").Copy Destination:=Range("A2")

I have this at present (which pretty much came from the macro recorder):

Range("A1").Copy
Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Is this the fastest way for doing a special paste for one cell? I would
have thought that there would be arguments (is that the right word?) that I
could specify to the first one so that I don't actually have to select the
range that I want to paste into. Any help is greatly appreciated.

Best Regards,


CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk
 
You aren't selecting the destination now since you are using the range
format of pastespecial without selecting the destination. There is no
pastespecial argument to the copy command.

If you just want to get the value you could do

Range("A2").Value = Range("A1").Value
 
I have this at present (which pretty much came from the macro recorder):

Range("A1").Copy
Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Click to expand...

Tom's response is the correct solution. But when you do need PasteSpecial
you can simply the above to:

Range("A1").Copy
Range("A2").PasteSpecial Paste:=xlValues

Don <donwiss at panix.com>.
 
Hey up Tom,

Think it works out about twice as slow on my pc (when I've timed it
before).Never do massive loops so maybe I lose a couple of minutes a
day.

What do you mean when you say that [A1] is a 'hardcoded range' and
range("A1").value isn't?

Jason.


Tom Ogilvy said:
If you want it to be at 4 times slower than

Range("A2").Value = Range("A1").Value

and only need to deal with a hardcoded range.

--
Regards,
Tom Ogilvy

jason said:
Suppose:

[A2] = [A1]

would do?!

J

"CalumMurdo Kennedy" <[email protected]> wrote in message
Click to expand...
Click to expand...
 
Back
Top