"bpascal123" wrote in message
...
Hi,

I replaced
wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).value

with
wks2.Range(wks2.Cells(1, 1), wks2.Cells(10, 1)) =
wks1.Range(wks1.Cells(1, 1), wks1.Cells(10, 3)).Value

Honestly, i haven't been able to see i needed to link the cell
property in a range to a specific worksheet. Because to me, the range
object is already linked to a specific worksheet then the cells
property in the range object doesn't to be linked to a specific range
on a worksheet



Pascal, does the built-in help make sense to you? I found that it took
me quite a significant period of time before the object model and the
distinctions between objects, properties and methods began to actually
make sense to me. By "quite a significant period of time" memory tells
me that means some months, not days or even a few weeks.

From the VBE window enter [ range property ] in the help search box --
you should get a list of articles to choose from. Quoting from that help
text:

<qWhen used without an object qualifier, this property is a shortcut
for ActiveSheet.Range (it returns a range from the active sheet; if the
active sheet isn't a worksheet, the property fails).</q

In other words, when you use Range (or Cell) in code without a
qualifier, VBA uses the ActiveSheet object -- which may or may not be
what you expect it to be.

What you said above is an excellent illustration of why it is necessary
to develop an understanding of the rules that govern the compiler. In
this case, what seems to be "common sense" to you is in fact a violation
of the rules that the complier follows.

wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1),
Cells(10, 3)).value

*cannot* work, because you are expecting the Cells property to return
cells from two different worksheets ... but there is only one
AcitveSheet (and it could easily be neither wks1 nor wks2!)

One of the things that was difficult for me to grasp is that there are
both Range properties and Range objects ... and they do entirely
different things. Let's see if I can summarize: The Cells property
returns a Range Object consisting of a single cell. The Range property
(many objects have a Range property- for instance: Excel Application
Window objects, Worksheet objects and Range objects to name three) also
returns a Range object - which may contain one or many cells, included
in one or many areas. VBA rules allow you to use Range and Cells with or
without object qualifiers - but if you do not qualify (disambiguate) the
default object is always the active sheet.

HTH!

--
Clif McIrvin


(clare reads his mail with moe, nomail feeds the bit bucket :-)