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

Question

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.

maybe i got it wrong, but when setting a sheet-object the parent (workbook) is includet... having workbook_object.sheet_object normally leads to an error... — Dirk Reichel
Thanks for the comment @DirkReichel, you are right, that solve part of the issue. Now this code is working: shtDestinationSheet.Range("A:C").Value = Workbooks(SourceWorkBook).Sheets("SourceSheet").Range("A:C").Value and the semi dinamic range is also working: shtDestinationSheet.Range("A1:C" & lastRow).Value = Workbooks(SoureceWorkBook).Sheets("SourceSheet").("A1:C" & lastRow).Value . But I cannot still use full dynamic ranges using cells(): .Range(Cells(1, 1), Cells(lastRow, lastCol)) — A Torre
normally there should be no problem... what error did you get? does this still happen if you use a string for Range() like str = Range(Cells(1, 1), Cells(lastRow, lastCol)).Address and then simply use .Range(str)? — Dirk Reichel
Thanks for the quick reponse @DirkReichel , you solution works: Str = Range(Cells(1, 1), Cells(lastRow, lastCol)).Address and shtDestinationSheet.Range(Str).Value = Workbooks(SourceWorkBook).Sheets("SourceSheet").Range(Str).Value. But replacing Range(Str) with Range(Cells(1, 1), Cells(lastRow, lastCol)) I got this error: Run-Time Error '1004' Application-defined or object-defined error — A Torre

Dirk Reichel Dirk Reichel · Accepted Answer · 2015-11-12T13:20:33

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)

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

1 Answers