By Aziz


2011-11-08 08:55:07 8 Comments

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

3 comments

@chris neilsen 2011-11-08 09:35:59

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

@Aziz 2011-11-08 09:50:58

Of Course! You are amazing! :D Thanks a lot. I should use Sheets("Temp").Range(Sheets("Temp").Cells(1), Sheets("Temp").Cells(1).End(xlDown)).Copy . But your version is compact and better formed so I catched it. :)

@GMalc 2017-11-14 21:34:32

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")

@Anonymous 2015-01-15 23:29:52

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.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Is there a way to crack the password on an Excel VBA Project?

  • 2009-06-22 10:37:13
  • Jonathan Sayce
  • 1005124 View
  • 435 Score
  • 22 Answer
  • Tags:   excel vba passwords

13 Answered Questions

[SOLVED] How to avoid using Select in Excel VBA

  • 2012-05-23 05:57:58
  • BiGXERO
  • 235031 View
  • 475 Score
  • 13 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Paste range of values to other sheet

  • 2018-06-28 10:23:26
  • ChartProblems
  • 39 View
  • 1 Score
  • 1 Answer
  • Tags:   excel-vba vba excel

1 Answered Questions

Excel loop copy a range in sheet 1 and paste to sheet 2

2 Answered Questions

[SOLVED] Excel VBA copy range and cells

1 Answered Questions

[SOLVED] Excel VBA: copying raw data into specific cells of a target sheet

1 Answered Questions

Looping incorrectly Excel VBA

0 Answered Questions

Excel VBA loop module sheet out of range?

  • 2017-07-26 20:21:52
  • Alister M
  • 70 View
  • 0 Score
  • 0 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] Copy range of cells from one sheet to another

  • 2017-03-05 08:40:58
  • Molar Bear
  • 327 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

2 Answered Questions

[SOLVED] Transposing cells in a loop using VBA in excel

Sponsored Content