Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What would be the most effective way to do this? | Excel Worksheet Functions | |||
Make your chart more effective | Charts and Charting in Excel | |||
More effective than DAYS360? | Excel Worksheet Functions | |||
Effective Rate of Return | Excel Discussion (Misc queries) | |||
pastespecial | Excel Programming |