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

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

On Tue, 12 Oct 2004 17:47:07 -0700, "Ken Loomis" wrote:

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

Thanks, Myrna.

That was it.

Ken Loomis

"Myrna Larson" wrote in message
...
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

On Tue, 12 Oct 2004 17:47:07 -0700, "Ken Loomis"
wrote:

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