Question
This script works fine when I'm viewing the "Temp" sheet. But when I'm in
another sheet then the copy command fails. It gives an Application-defined or object-defined error:
Sheets("Temp").Range(Cells(1), Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial
I can use this script instead, but then I have problems with pasting it:
Sheets("Temp").Columns(1).Copy
Sheets("Overview").Range("C40").PasteSpecial
I don't want to activate the "Temp" sheet to get this.
What else can I do?
Answer
Your issue is that the because the Cell references inside the Range 's are
unqualified, they refer to a default sheet, which may not be the sheet you
intend. For standard modules, the ThisWorkbook module, custom classes and
user form modules, the defeault is the ActiveSheet. For Worksheet code
behind modules, it's that worksheet.
For modules other than worksheet code behind modules, your code is actually saying
Sheets("Temp").Range(ActiveSheet.Cells(1), ActiveSheet.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial
For worksheet code behind modules, your code is actually saying
Sheets("Temp").Range(Me.Cells(1), Me.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial
In either case, the solution is the same: fully qualify the range references with the required workbook:
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = ActiveWorkbook.Sheets("Temp")
Set sh2 = ActiveWorkbook.Sheets("Overview")
With sh1
.Range(.Cells(1,1), .Cells(1,1).End(xlDown)).Copy
End With
sh2.Range("C40").PasteSpecial
Note: When using .End(xlDown) there is a danger that this will result in
a range extending further than you expect. It's better to use .End(xlUp) if
your sheet layout allows. If not, check the referenced cell and the cell below
for Empty first.
