Hello,
Here's a code I am using to sort data each time the workbook opens.
This code runs fine for a while, and then, without changing the code,
stops working. When it stops, the error I get is: Run time error
1004. Application defined or object defined error. This seems like a
very simple code to write, and it does work sometimes. Can some
explain why it intermittently fails? If not, can you recommend a code
which will do the same task without fail?

thanks.
Andrew


Private Sub workbook_open()
Worksheets("data").Range(Cells(2, 1), Cells(50000, 7)).Sort
Key1:=Worksheets("data").Range("D2")
end Sub

Those unqualified ranges (cells(2,1), cells(5000,7)) refer to the active
sheet--and that may not always be Data.

Private Sub workbook_open()
with Worksheets("data")
.Range(.Cells(2, 1), .Cells(50000, 7)).Sort _
Key1:=.Range("D2")
end with
end Sub


Andrew wrote:
>
> Hello,
> Here's a code I am using to sort data each time the workbook opens.
> This code runs fine for a while, and then, without changing the code,
> stops working. When it stops, the error I get is: Run time error
> 1004. Application defined or object defined error. This seems like a
> very simple code to write, and it does work sometimes. Can some
> explain why it intermittently fails? If not, can you recommend a code
> which will do the same task without fail?
>
> thanks.
> Andrew
>
> Private Sub workbook_open()
> Worksheets("data").Range(Cells(2, 1), Cells(50000, 7)).Sort
> Key1:=Worksheets("data").Range("D2")
> end Sub

--

Dave Peterson

Private Sub workbook_open()
With Worksheets("data")
.Range(.Cells(2, 1), .Cells(50000, 7)).Sort
Key1:=.Range("D2")
End With
end Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Andrew" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
> Here's a code I am using to sort data each time the workbook opens.
> This code runs fine for a while, and then, without changing the code,
> stops working. When it stops, the error I get is: Run time error
> 1004. Application defined or object defined error. This seems like a
> very simple code to write, and it does work sometimes. Can some
> explain why it intermittently fails? If not, can you recommend a code
> which will do the same task without fail?
>
> thanks.
> Andrew
>
>
> Private Sub workbook_open()
> Worksheets("data").Range(Cells(2, 1), Cells(50000, 7)).Sort
> Key1:=Worksheets("data").Range("D2")
> end Sub
>