Excel VBA, getting range from an inactive sheet

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 a "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 dont want to activate the "Temp" sheet to get this

What else can I do

Answer 1

I encountered a problem like this myself: I was trying to search through a separate worksheet to see if the color of a cell matched the color of a cell in a list and return a string value: if you are using .Cells(row, column), you only need this: Sheets("sheetname").Cells(row, column) to reference that range of cells.

I was looping through a block of 500 cells and it works surprisingly quickly for me.

I have not tried this with .Copy, but I would assume it would work the same way.

Answer 2

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

Answer 3

This will do, I don't like to use (xlDown) in case a cell is empty.

Dim lRow As Long
lRow = Sheets("Temp").Cells(Cells.Rows.Count, "A").End(xlUp).Row

With Sheets("Temp")
     .Range("A1:A" & lRow).Copy Sheets("Overview").Range("C40")
End With

Or if you want to just use Columns...

Sheets("Temp").Columns(1).SpecialCells(xlCellTypeConstants).Copy Destination:=Sheets("Overview").Range("C40")