Friday, January 26, 2018

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



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)


No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...