Hello,

I have made a VBA program to update a two pivot tables and two
autofilters in excel 2000, no problems. But in excel 97 give the
program a error 1004. The first autofilter gives no problems. But the
second autofilter give a error like "Autofilter method of Range Class
Failed" or excel gives a failed error and quits. Can somebody help me
out?

gr.
Fons Heuven




Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Tabel E").Select
Selection.AutoFilter Field:=1, Criteria1:="<0", Operator:=xlAnd,
Criteria2:="<"

Sheets("Tabel E2").Select

Application.DisplayAlerts = False
ActiveSheet.PivotTables("Draaitabel2").RefreshTab le
Application.DisplayAlerts = True
CommandButton1.TakeFocusOnClick = False
ActiveCell.Activate
Sheets("tabel E2").Range("a14").Select

--Selection.AutoFilter Field:=1, Criteria1:="<0", Operator:=xlAnd,
Criteria2:="<"

Sheets("Tabel tot").Select
ActiveSheet.PivotTables("Draaitabel3").RefreshTab le
Application.ScreenUpdating = True
Sheets("Hoofdmenu").Select
End Sub

I don't know why you are getting the error, but I would try modifying the code
to remove any existing filter before you apply a new one. I don't know what
that code looks like off-hand. Turn on the macro recorder and do it manually
to find out.

On 12 Oct 2004 06:49:59 -0700, (fonz) wrote:

Hello,

I have made a VBA program to update a two pivot tables and two
autofilters in excel 2000, no problems. But in excel 97 give the
program a error 1004. The first autofilter gives no problems. But the
second autofilter give a error like "Autofilter method of Range Class
Failed" or excel gives a failed error and quits. Can somebody help me
out?

gr.
Fons Heuven




Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Tabel E").Select
Selection.AutoFilter Field:=1, Criteria1:="<0", Operator:=xlAnd,
Criteria2:="<"

Sheets("Tabel E2").Select

Application.DisplayAlerts = False
ActiveSheet.PivotTables("Draaitabel2").RefreshTa ble
Application.DisplayAlerts = True
CommandButton1.TakeFocusOnClick = False
ActiveCell.Activate
Sheets("tabel E2").Range("a14").Select

--Selection.AutoFilter Field:=1, Criteria1:="<0", Operator:=xlAnd,
Criteria2:="<"

Sheets("Tabel tot").Select
ActiveSheet.PivotTables("Draaitabel3").RefreshTa ble
Application.ScreenUpdating = True
Sheets("Hoofdmenu").Select
End Sub