You are correct. Range has PasteSpecial but no Paste, for some unknown
reason. Glad I said "not tested" in my reply. :-)
The copy works in Sheet1 without a select of that sheet, because it is a
copy, not a select.
The select of the Sheet2 cell without a select of Sheet1 first fails because
you can't select something on a sheet where the sheet itself is not
selected.
You could put the select of Sheet2 back into the code.
Alternatively, you can use PasteSpecial in the way you have, which I think
is a better approach.
Thirdly, there is a variant of the Copy, which supplies a destination ...
Sheets("Sheet1").Range("A3:C3").Copy
(Destination:=Sheets("Sheet2").Range("A7"))
... note that Destination has a colon and an equals after it because it is a
named parameter ...
again, not tested, but this will hopefully :-) work OK.
Regards, Dave S
----- Original Message -----
From: "Michael Trombetta" <mickey11030@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, January 13, 2012 3:27 AM
Subject: RE: [ExcelVBA] Annother Puzzle
Thank you David for your suggestion, although I don't understand it. You say
the problem is a lack of select of Sheet2, but the first statement works
without a select for Sheet1. Nevertheless I implemented your suggestion of
collapsed the last 2 statements into
Sheets("Sheet2").Range("A7").Paste
and believe it or not that fails with the error message: Object doesn't
support this property or method.
I used the Object Browser (of which I understand about 2%) and it seems to
say that the Range object does NOT have a Paste method! It does have a
PasteSpecial method. So I recorded a macro using PasteSpecial (to get the
format) and changed the last statement to
Sheets("Sheet2").Range("A7").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
and that works even when Sheet3 is active when the subroutine is run.
Why Microsoft decided that the Range object should not have a Paste method
but should have a PasteSpecial method is a question for another day.
Thanks again.
Mickey
[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
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1416 / Virus Database: 2109/4137 - Release Date: 01/11/12
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