excel - "Run-Time error '438': Object doesn't support this property or method." Range.values = Range.values

itemprop="text">

I am trying to copy a range of data
from a Excel workbook to another workbook without the need of selecting any workbook
during this process and using worksheet object names.

I want to do this because the selection
process:

Windows("SourceWorksheet").Activate - Sheet("SourceSheet").Select -

Range("SourceRange").Copy - Windows("DestinationWorksheet").Activate
-
Sheet("DestinationSheet").Select -
Range("DestinationRange").Paste

is
very slow compare
with

DestinationWorkBook.DestinationSheet.Range("DestinationRange").Value
=

SourceWorkBook.SourceWorkSheet.Range("SourceRange").Value

I
have got this working using sheets tap names and letter
ranges:

Workbooks(DestinationWorkBook).Sheets("DestinationSheet").Range("A:C").Value
            = _

            Workbooks(SoureceWorkBook).Sheets("SourceSheet").Range("A:C").Value

And
also using semi-dynamic ranges and sheets tap
names:

lastRow =
            Cells(Workbooks(Limits_Name).Sheets("SourceSheet").Rows.Count, _

            "A").End(xlUp).Row
Workbooks(DestinationWorkBook).Sheets("DestinationSheet").Range("A1:C"
            & lastRow).Value = _

            Workbooks(SourceWorkBook).Sheets("SourceSheet").Range("A1:C" &
            lastRow).Value

My
problems starts when I use sheets object names instead of sheets names or cells instead
of ranges. In those situation is when I get that
error:

Workbooks(DestinationWorkBook).shtDestinationSheet.Range("A:C").Value
            = _
 Workbooks(SourceWorkBook).Sheets("SourceSheet").Range("A:C").Value
            

OR

lastRow =
            Cells(Workbooks(SourceWorkBook).Sheets("SourceSheet").Rows.Count,
            "A").End(xlUp).Row
lastCol = Cells(1,
            Workbooks(SourceWorkBook).Sheets("SourceSheet").Columns.Count).End(xlToLeft).Column
Workbooks(DestinationWorkBook).Sheets("DestinationSheet").Range(Cells(1,
            1), Cells(lastRow, lastCol)).Value = _

            Workbooks(SourceWorkBook).Sheets("SourceSheet").Range(Cells(1, 1), Cells(lastRow,
            lastCol)).Value


OR (this is the ideal
            code)

lastRow =
            Cells(Workbooks(SourceWorkBook).Sheets("SourceSheet").Rows.Count,
            "A").End(xlUp).Row
lastCol = Cells(1,
            Workbooks(SourceWorkBook).Sheets("SourceSheet").Columns.Count).End(xlToLeft).Column

Workbooks(DestinationWorkBook).shtDestinationSheet.Range(Cells(1,
            1), Cells(lastRow, lastCol)).Value = _

            Workbooks(SourceWorkBook).Sheets("SourceSheet").Range(Cells(1, 1), Cells(lastRow,
            lastCol)).Value

I
would like to know what is the difference between using Sheets("sheetname") and the and
the sheet object name which can be given under the (name) property of the worksheet
object properties.

If I use
Sheets("SourceSheet").Range("") I do not need to select the sheet but using
sthSourceSheet.Range("") I do.

I like to use
sheet object names because the VBA code still works if the sheet name is
modified.

Answer

First problem (solved):
When using
an object for a Worksheet this includes also the
Workbook.
While the
Worksheet-Object is not a child of the workbook itself inside
of the syntax like Workbook.Worksheet_Object. So either use
Workbook.Worksheet(Worksheet_Object.Name) or just
Worksheet_Object

Second
probem (solved):
There is a problem using Range(Cells(),
Cells()) in a non-active workbook... Using only
Cells() with no parent sometimes causes trouble cus VBA want's
to use a full path. Just Cells will retun a
[workbook]Sheet!Range while using with a different parent this causes an error. VBA will
get a return like:
Wb1.Ws1.Range(Wb2.Ws2.Range).

You
can try something
like:

htDestinationSheet.Range(htDestinationSheet.Cells(1,
            1), htDestinationSheet.Cells(lastRow, lastCol)).Value =
            Workbooks(SourceWorkBook).Sheets("SourceSheet").Range(Workbooks(SourceWorkBook).Sheets("SourceSheet").Cells(1,
            1), Workbooks(SourceWorkBook).Sheets("SourceSheet").Cells(lastRow,
            lastCol)).Value

which
should work... However: i think it's better to stay with str
(looks better)