Autofilter

[wittmannm]

I have a workbook to keep track of Financial data for my department. Autofilter is turned on, when I change the filter I need the subtotal to change only I am using sumproduct instead of subtotal. Here is the formula =IF(ISBLANK($A$3),SUMPRODUCT(($F$10:$F$472=$F477)*($H$10:$H$472)),SUMPRODUCT(($F$10:$F$472=$F477)*($B$10:$B$472=$A$3)*($ H$10:$H$472)))

Cell A3 is to be updated with the new criteria filter, the problem is that cell A3 will not update until I click on the worksheet. Below is my code and everything seems to work except for the updating of cell A3. Can you tell me what I am missing.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Changefilters

End Sub

Public Sub Changefilters()
Dim w As Worksheet
Dim filterarray()


Set w = Worksheets("INS fy03")

With w.AutoFilter
With .Filters
For f = 1 To .Count
With .Item(f)
If .On Then
w.Range("A3").Value = .Criteria1
End If
End With
Next
End With

End With

End Sub

[manet]

Try using Private Sub Worksheet_Calculate()

[Aladin Akyurek]

And, I'd suggest changing

=IF(ISBLANK($A$3),SUMPRODUCT(($F$10:$F$472=$F477)*($H$10:$H$472)),SUMPRODUCT(($F$10:$F$472=$F477)*($B$10:$B$472=$A$3)*($ H$10:$H$472)))

to

=IF(ISBLANK($A$3),SUMIF($F$10:$F$472,$F477,$H$10:$H$472),SUMPRODUCT(($F$10:$F$472=$F477)*($B$10:$B$472=$A$3)*($H$10:$H$4 72)))

Aladin