Just another note.
When you are using the copy/paste methods, you're actually using the Windows
Clipboard.
This copies a LOT of supporting information, like Cell formatting, colors,
borders, fonts.
and it copies it not once, but TWICE (once to the clipboard, then again to the
destination sheet)
Not only that, but if you have a macro that runs for a long time (I have one
application that has over 10,000 lines of VBA code that runs for about 45
minutes to produce daily reports) then while it's running, you CANNOT use
another application that requires the clipboard.
That means that if your Excel macro is running, and you try to catch up on email
and happen to copy/paste a link into the email, it's possible that you could
overwrite the clipboard that your macro is using...
If you are only interested in the CONTENT, and not the formatting,
it's quicker to simply copy the range values from one sheet directly to the
second:
This command copied 10 columns and 633,000 rows in about 10 seconds:
Sheets("Sheet2").Range("A1:K633294").Value =
Sheets("Sheet1").Range("A1:K633294").Value
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: "david.smart@ubs.com" <david.smart@ubs.com>
To: ExcelVBA@yahoogroups.com
Sent: Thu, January 12, 2012 1:19:55 AM
Subject: RE: [ExcelVBA] Annother Puzzle
I doubt that it is the module. I think it's the lack of a select of
Sheet2.
You are selecting cells on Sheet2, when Sheet2 isn't active. This will
fail.
Either reinstate the Select for Sheet2, or ... better ... remove the
select of the second range. Try collapsing further:
Sheets("Sheet1").Range("A3:C3").Copy
Sheets("Sheet2").Range("A7").Paste
(not tested, but should be OK).
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of mickey11030
Sent: Thursday, 12 January 2012 11:18
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Annother Puzzle
I want to copy several cells from one sheet to a different sheet in the
same workbook. I recorded the following macro:
Sheets("Sheet1").Select
Range("A3:C3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A7").Select
ActiveSheet.Paste
To simplify this code, I collapsed the first 3 lines into a single line
Sheets("Sheet1").Range("A3:C3").Copy
and the next 2 lines into:
Sheets("Sheet2").Range("A7").Select
and didn't change the last line. So the final macro is
Sheets("Sheet1").Range("A3:C3").Copy
Sheets("Sheet2").Range("A7").Select
ActiveSheet.Paste
This new macro works perfectly if when I start it Sheet2 is the active
sheet, but it fails if Sheet1 is the active sheet, with the error
message:
Application-defined or object-defined error
I should say that I remember the lesson Paul taught me about placing
code in the appropriate module. This code is in "This Workbook" module.
Can anyone explain this?
Thanks.
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar