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
----------------------------------
Tidak ada komentar:
Posting Komentar