Jay, Cells also has a parent object, which is a sheet. You are in
effect actually saying this:
Sheets("Sheet2").Range(ActiveSheet.Cells(2, 1),
ActiveSheet.Cells(endrow, 7)).ClearContents 'which makes no sense to
excel
This will work:
With Sheets("Sheet2")
.Range(.Cells(2,1),.Cells(endrow,7)).ClearContents
End With

Charles Chickering
jayklmno wrote:
If I am referring to a range not on the active sheet, why does this work
triggered from a Macro button on Sheet1...

Sheets("Sheet2").Range("A2:G57").ClearContents

But this will not?

Sheets("Sheet2").Range(Cells(2, 1), Cells(endrow, 7)).ClearContents

If I am on Sheet2, the above code works.

Why?