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.