A More Effective Way of Using PasteSpecial?
 

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

A More Effective Way of Using PasteSpecial?
 

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

--
Regards,
Tom Ogilvy


CalumMurdo Kennedy wrote in message
...
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

A More Effective Way of Using PasteSpecial?
 

On Sun, 12 Oct 2003, CalumMurdo Kennedy wrote:

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

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.

A More Effective Way of Using PasteSpecial?
 

Thanks Guys,

It's funny how i manage to avoid the easiest way of doing things!

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

"Don Wiss" wrote in message
...
On Sun, 12 Oct 2003, CalumMurdo Kennedy wrote:

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

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.

A More Effective Way of Using PasteSpecial?
 

Suppose:

[A2] = [A1]

would do?!

J

"CalumMurdo Kennedy" wrote in message ...
Thanks Guys,

It's funny how i manage to avoid the easiest way of doing things!

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

"Don Wiss" wrote in message
...
On Sun, 12 Oct 2003, CalumMurdo Kennedy wrote:

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

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.

A More Effective Way of Using PasteSpecial?
 

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" wrote in message
om...
Suppose:

[A2] = [A1]

would do?!

J

"CalumMurdo Kennedy" wrote in message
...
Thanks Guys,

It's funny how i manage to avoid the easiest way of doing things!

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

"Don Wiss" wrote in message
...
On Sun, 12 Oct 2003, CalumMurdo Kennedy
wrote:

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

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.

A More Effective Way of Using PasteSpecial?
 

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" wrote in message ...
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" wrote in message
om...
Suppose:

[A2] = [A1]

would do?!

J

"CalumMurdo Kennedy" wrote in message
...
Thanks Guys,

It's funny how i manage to avoid the easiest way of doing things!

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

"Don Wiss" wrote in message
...
On Sun, 12 Oct 2003, CalumMurdo Kennedy
wrote:

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

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.

A More Effective Way of Using PasteSpecial?
 

Search for 'Shortcut Range References' on this WebPage.

http://msdn.microsoft.com/library/de..._chap05idx.asp

"This shortcut is convenient when you want to refer to an absolute
range. However, it is not as flexible as the Range property as it
cannot handle variable input as strings or object references."

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
What do you mean when you say that [A1] is a 'hardcoded range' and
range("A1").value isn't?