Cells reference to another worksheet

[Giordano Bruno]

In my code I have the following two lines:
Sheets("PersStore").Range("A2:A84").Copy
Sheets("PersStore").Range(Cells(2, 1), Cells(84, 1)).Copy
The code breaks at the second line with the message "Run-time error 1004 Application-defined or object-defined error"
The second line runs if I first make PersStore the active worksheet. I want to use the second line, but I don't want to have to activate the other worksheet. Can anyone point me in the right direction?

 

[bebo021999]

Try:

Worksheets("Sheet1").Range("A" & 2 & ":A" & 84).Copy

 

[Giordano Bruno]

Thanks Bebo, but I'm curious to know why the Cells method doesn't work?

 

[nemmi69]

change it to

Sheets("PersStore").Range(Sheets("PersStore").Cells(2, 1), Cells(84, 1).Address).Copy

 

[bebo021999]

Thanks Bebo, but I'm curious to know why the Cells method doesn't work?

Assume active sheet is Sheet1
With this line

Sheets("PersStore").Range(Cells(2, 1), Cells(84, 1)).Copy

excel think Cells(2, 1) is cell A2 of current sheet "Sheet1"

Should be:

Sheets("PersStore").Range(Sheets("PersStore").Cells(2, 1), Sheets("PersStore").Cells(84, 1)).Copy

or shorter:

With Sheets("PersStore")
    .Range(.Cells(2, 1), .Cells(84, 1)).Copy
End With

with the dot "." present Sheets("PersStore") properties

 

[Giordano Bruno]

Heartfelt thanks Bebo. Problem solved.