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
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