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?




Assuming endrow=57, because Cells(2,1) and Cells(endrow,7) are, by
default, referring to cells on the active sheet.

There are several ways to avoid the problem. Perhaps the clearest that
indicates the difficulty is

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

A common fix is

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

I prefer

Set rng = Sheets("Sheet2").Range("A1")
Range(rng(2, 1), rng(endrow, 7)).ClearContents

Alan Beban