1

I want to copy and paste a dynamic Range. I try to run this code but it does not work.

Instead

"Runtime error 1004"

appears.

Worksheets("Data").Range(Cells(1, 1), Cells(10, 2)).Copy Worksheets("Tabelle1").Range(Cells(1, 1), Cells(10, 2))

If I use Range("A1:A5"), it works fine.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jonah Ho
  • 45
  • 4

1 Answers1

2

The issue is that VBA does not know in which worksheet these cells Cells(1, 1), Cells(10, 2) are because you did not specify it. Therefore you get an error.

Make sure you specify a worksheet for every Cells, Range, Rows or Columns object. If you don't specify it Excel starts guessing which sheet you mean (based on the scope the code is written in), and it might assume something else than you did. In most cases a Cells without specifying a sheet will be the same as ActiveSheet.Cells.

So correct would be:

Worksheets("Data").Range(Worksheets("Data").Cells(1, 1), Worksheets("Data").Cells(10, 2)).Copy Worksheets("Tabelle1").Range(Worksheets("Tabelle1").Cells(1, 1), Worksheets("Tabelle1").Cells(10, 2))

Or better:

Dim Source As Range
With ThisWorkbook.Worksheets("Data")
    Set Source = .Range(.Cells(1, 1), .Cells(10, 2))
End With

Dim Destination As Range
With ThisWorkbook.Worksheets("Tabelle1")
    Set Destination = .Range(.Cells(1, 1), .Cells(10, 2))
End With

Source.Copy Destination

or something like

Dim wsSrc As Worksheet
Set wsSrc = ThisWorkbook.Worksheets("Data")

Dim wsDest As Worksheet
Set wsDest = ThisWorkbook.Worksheets("Tabelle1")

wsSrc.Range(wsSrc.Cells(1, 1), wsSrc.Cells(10, 2)).Copy wsDest.Range(wsDest.Cells(1, 1), wsDest.Cells(10, 2))
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    Was about to write more or less the same, just 2 remarks: **(1)** Using `Set Source = .Cells(1, 1).Resize(10, 2)` could be an alternative. **(2)** For the destination it's sufficient to provide only the top left cell. – FunThomas Dec 07 '20 at 08:43
  • 1
    Ah, and a third remark: `Worksheets` should also be qualified, eg `With ThisWorkbook.Worksheets("Data")` – FunThomas Dec 07 '20 at 08:45
  • @FunThomas Good points, I added `ThisWorkbook`. Of course `.Resize` would be a good option too. Depending on whether the cells are hard coded (like above) or retrieved in some other way (OP spoke about dynamic range), `.Resize` might be either esier or more cumbersome to use. If hard coded I would prefer `.Resize`. – Pᴇʜ Dec 07 '20 at 08:59