Application-defined or object-defined error

[Ken Loomis]

I get an error in this macro.

It is listed as

"Run-time error ('1004':

Application-defined or object-defined error"


The error is caused by this line:

.Range(Cells(4, 1), Cells(totalrows, 6)).Clear


Sub clearStreetSummary()
Dim totalrows As Integer

With Sheets("Street Summary")
totalrows = .UsedRange.Rows.count + 2
If totalrows > 3 Then
.Range(Cells(4, 1), Cells(totalrows, 6)).Clear
End If
End With

End Sub



I was certain this macro ran at least once.

Can someone please help with this?

Ken Loomis

 

[Myrna Larson]

Street Summary is probably not the active sheet when the macro runs. If that's
the case, you are trying to define a range on that sheet (Street Summary)
using references to cells on a different sheet, namely the ACTIVE sheet. Won't
work, of course.

I suspect you forgot the periods in front of the two Cells properties. Try it
like this and report back, please:

Sub clearStreetSummary()
Dim totalrows As Integer

With Sheets("Street Summary")
totalrows = .UsedRange.Rows.count + 2
If totalrows > 3 Then
.Range(.Cells(4, 1), .Cells(totalrows, 6)).Clear
End If
End With
End Sub

 

[Ken Loomis]

Thanks, Myrna.

That was it.

Ken Loomis

Street Summary is probably not the active sheet when the macro runs. If
that's
the case, you are trying to define a range on that sheet (Street Summary)
using references to cells on a different sheet, namely the ACTIVE sheet.
Won't
work, of course.

I suspect you forgot the periods in front of the two Cells properties. Try
it
like this and report back, please:

Sub clearStreetSummary()
Dim totalrows As Integer

With Sheets("Street Summary")
totalrows = .UsedRange.Rows.count + 2
If totalrows > 3 Then
.Range(.Cells(4, 1), .Cells(totalrows, 6)).Clear
End If
End With
End Sub