Hi Chip,

Understood.

I use the With extensively.

I tried to shorten the code and the dots and overlooked that.

Many thanks for the explanation that is very relevant.

Regards,

Antonio

"Chip Pearson" wrote:

Antonio,

In the statement


Worksheets("pivot").Range(Cells(number_s + 3, 1), _
Cells(number_ + 2, 5)).ClearContents

The Cells property is NOT refering the cells on the Pivot
worksheet. They are refering to cells on the Active Sheet. If the
"Pivot" worksheet is not the Active Sheet, you'll get an Error
1004. You need to make the Cells properties reference the Pivot
sheet. Use a With statement as follows:

With Worksheets("pivot")
.Range(.Cells(number_s + 3, 1), _
.Cells(number_ + 2, 5)).ClearContents
End With


Note the "." character before "Range" and both "Cells".

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Antonio" wrote in message
...
The following works fine

Worksheets("EQ by P").Rows((number_s+ 4) & ":" & (number_ +
3)).ClearContents

The following does not (one line)
Worksheets("pivot").Range(Cells(number_s + 3, 1), Cells(number_
+ 2,
5)).ClearContents

The following does (two lines):
Worksheets("pivot").Activate

Range(Cells(number_s + 3, 1), Cells(number_ + 2,
5)).ClearContents

Why is that?

When is "Activate" required?



Why is the following not working?