Excel VBA copy to another range using cells

Question

I'm having some trouble with my code:

Worksheets("Week 1").Range(Cells(4, ColNo), Cells(5, ColNo)).Value = Worksheets("Count").Range(Cells(4, 2), Cells(5, 2)).Value

If I change it to

Worksheets("Week 1").Range(Cells(4, ColNo), Cells(5, ColNo)).Value = Worksheets("Count").Range("B4:B5").Value

It works fine, can anyone help?

`Cells` is not fully qualified so it refers to the currently active sheet — Slai, Feb 27 '17 at 00:29
Thanks Robin, adding .address after the cells function worked. — Cr1kk0, Feb 27 '17 at 00:58

score 0 · Answer 1 · answered Feb 27 '17 at 00:59

0

I fixed this by adding .address after the cells

Worksheets("Count").Range(Cells(4, 2).address, Cells(5, 2).address).Value

answered Feb 27 '17 at 00:59

Cr1kk0

If that code runs when the active sheet is a `Chart`, it will fail because a `Chart` sheet doesn't have `Cells`. It is better to fully qualify the references, e.g. as `Worksheets("Count").Range(Worksheets("Count").Cells(4, 2), Worksheets("Count").Cells(5, 2)).Value` (or using a `With Worksheets("Count")` block, `.Range(.Cells(4, 2), .Cells(5, 2)).Value`) – YowE3K Feb 27 '17 at 01:06

1 Answers1