1

I want to use "=" operator to transfer data from "Sheet1" to "Sheet2" because it is fast. However, the following code doesn't work: 

Sheets("Sheet2").range(cells(1, 1),cells(1,5)).value = Sheets("Sheet1")
.range(cells(1, 1),cells(1,5)).value

Why?

Murat Seker
  • 890
  • 2
  • 14
  • 26
swen
  • 13
  • 3

2 Answers2

2

If Range and Cells are not fully qualified, then they will be taken from ActiveSheet.

So the code line

Sheets("Sheet2").range(cells(1, 1),cells(1,5)).value = Sheets("Sheet1").range(cells(1, 1),cells(1,5)).value

is the same as

Sheets("Sheet2").range(ActiveSheet.cells(1, 1),ActiveSheet.cells(1,5)).value = Sheets("Sheet1").range(ActiveSheet.cells(1, 1),ActiveSheet.cells(1,5)).value

This fails because either the cells of Sheet2 or the cells of Sheet1 cannot be from ActiveSheet. Maybe both are not if neither Sheet2 nor Sheet1 is the active sheet.

So we must fully qualify the cells:

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(1, 1), Sheets("Sheet2").Cells(1, 5)).Value = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(1, 5)).Value

Better we are using Worksheet variables for better readability:

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 5)).Value = ws1.Range(ws1.Cells(1, 1), ws1.Cells(1, 5)).Value
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
-1

It does work if you do it cell by cell and loop through them.

Sheets("Sheet2").cells(1, 1).value = Sheets("Sheet1").cells(1,1).value

I am not sure if you can use = on a range but will have a look.You could copy and paste a range however.

tink
  • 11
  • 5