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]
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