1

While copying data from Sheet1 to Sheet2 I am getting

"Run-time error '1004': Application-defined or object-defined error"

I am using vba code:

lnRow = 33
Sheets("Sheet1").Range("A3:C14").Copy Destination:=Sheets("Sheet2").Range(Cells(lnRow + 3, 1))

How can I copy data from Sheet1 to Sheet2 using column numbers?

Improve this question
1
  • 2
    A range needs the first cell and the last cell, you are feeding only the first cell, use Cells instead like this: Destination:=Sheets("Sheet2").Cells(lnRow + 3, 1) Commented Jan 17, 2020 at 8:04

2 Answers 2

Reset to default
2

Actually the problematic part is Sheets("Sheet2").Range(Cells(lnRow + 3, 1)) and there are 2 issues with it.

1. Issue

For the Cells object there is not specified in which worksheet it is so VBA assumes ActiveSheet. It is the same as writing Sheets("Sheet2").Range(ActiveSheet.Cells(lnRow + 3, 1)).

Now if the active sheet is not Sheet2 then the code will fail because the Range object is not in the same sheet as the Cells object, and that is not allowed.

2. Issue

According to the documentation of the Worksheet.Range property it accepts 2 arguments Range(Cell1, Cell2) where the second is optional, but only if the first is an address string.

Cell1

  • A String that is a range reference when one argument is used.
  • Either a String that is a range reference or a Range object when two arguments are used.

So that means you can use the following syntax:

  • Range("Address")
  • Range("Address", "Address")
  • Range(Cells(row, column), Cells(row, column))

but you cannot use

  • Range(Cells(row, column))

because if only one argument is used it must be a string/address. Actually the last one does not exist because you then can use Cells directly without using Range: Cells(row, column)

So in your case that means you must use Cells directly:

Sheets("Sheet2").Cells(lnRow + 3, 1)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

If you want to use the cells method then the error is due to the usage of range(cells(3,1)). Instead use sheets("Sheet1").range(cells(3,1),cells(4,1)). Otherwise you can omit the cells and specify the range as usual e.g. range("C4:D5").

Improve this answer

2 Comments

There is no problem if the range is 10000x bigger on the source, when you copy 300 cells and paste just in one cell, excel will paste it anyway. Try it.
Actually this will fail. This code only works if the active sheet is Sheet1 because the cells Cells(3, 1), Cells(4, 1) refer to active sheet but the Range referst to Sheets("Sheet1"). Every objects referring to a cell or range should specify in which workbook/worksheet they are or you will easily run into unreliable code. • Actually you run into issue 1 of my answer and @Damian is right too, that's actually no problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.