Copy doesn't work

[Demirion]

Hi, this code works:

Worksheets("Ark1").Range(Cells(1, 1), Cells(lRow, lCol)).Copy Worksheets("Ark2").Range("A1")

but I want to copy data from the second row it doesn't work and error is displayed "Run-time error 1004. Application-definded or object-defined error":

Worksheets("Ark1").Range(Cells(2, 1), Cells(lRow, lCol)).Copy Worksheets("Ark2").Range("A1")

What went wrong?

 

[Fluff]

You need to qualify all ranges with the sheet name like

With Worksheets("Ark1")
   .Range(.Cells(1, 1), .Cells(lRow, lCol)).Copy Worksheets("Ark2").Range("A1")
End With

 

[Demirion]

But the first code works for Range(Cells(1, 1) and doesn't work for Range(Cells(2, 1). I want the second one to start working:

Worksheets("Ark1").[B]Range(Cells(2, 1), Cells(lRow, lCol)).Copy Worksheets("Ark2").Range("A1")

 

[Fluff]

Just use the same structure that I showed.
That code will only work if the relevant sheet is active.

 

[Demirion]

I want to copy from another workbook with multiple sheets and paste into a second workbook with multiple sheets.

With Workbook1
.Sheets("A").Range(.Cells(2, 1), .Cells(lRow, lCol))) Copy
ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues
End With

This is my code snippet. Why doesn't it work?

 

[Fluff]

Because you are not qualifying the ranges with the sheet name. It should be like

With Workbook1.Sheets("A")
   .Range(.Cells(2, 1), .Cells(lRow, lCol)).Copy
   ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues
End With

 

[Demirion]

Why is it only pasting one A2 cell to ThisWorkbook.Sheets("Ark1")?

Is it possible to do this for multiple sheets, e.g. like this:

With Workbook1
.Sheets("A").Range(.Cells(2, 1), .Cells(lRow, lCol))) Copy
ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues

.Sheets("B").Range(.Cells(2, 1), .Cells(lRow, lCol))) Copy
ThisWorkbook.Sheets("Ark2").Range("A2").PasteSpecial xlPasteValues

.Sheets("C").Range(.Cells(2, 1), .Cells(lRow, lCol))) Copy
ThisWorkbook.Sheets("Ark3").Range("A2").PasteSpecial xlPasteValues

End With

 

[Fluff]

You have not done what I showed.

 

[Demirion]

Unfortunately, in this form, it only pastes the data into cell A2

 With Workbook1.Sheets("A")
 .Range(.Cells(2, 1), .Cells(lRow, lCol)).Copy
 ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues
End With

Yes but is there a way to do this for a lot of sheets in an easier way?

Instead of this:

With Workbook1.Sheets("A")
 .Range(.Cells(2, 1), .Cells(lRow, lCol)).Copy
 ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues
End With

With Workbook1.Sheets("B")
 .Range(.Cells(2, 1), .Cells(lRow, lCol)).Copy
 ThisWorkbook.Sheets("Ark2").Range("A2").PasteSpecial xlPasteValues
End With

Something like this:

With Workbook1

.Sheets("A").Range(.Cells(2, 1), .Cells(lRow, lCol))) Copy
ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues

.Sheets("B").Range(.Cells(2, 1), .Cells(lRow, lCol))) Copy
ThisWorkbook.Sheets("Ark2").Range("A2").PasteSpecial xlPasteValues

End With

 

[Fluff]

You can nest with statements like

With Workbook1
   With .Sheets("A")
      .Range(.Cells(2, 1), .Cells(lRow, lCol)).Copy
      ThisWorkbook.Sheets("Ark1").Range("A2").PasteSpecial xlPasteValues
   End With

   With .Sheets("B")
    .Range(.Cells(2, 1), .Cells(lRow, lCol)).Copy
    ThisWorkbook.Sheets("Ark2").Range("A2").PasteSpecial xlPasteValues
   End With
End With