ExcelBanter - Worksheet

Filters belong to the worksheet--not the workbook.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c1 As String

If Target.Address = "$H$991" Then
Application.EnableEvents = False
If Target.Value = 10 Then
With Me
If .AutoFilterMode Then
With Me.AutoFilter.Filters(1)
If .On Then
c1 = .Criteria1
End If
Range("F992").Value = c1
End With
End If
End With
End If
Application.EnableEvents = True
End If
End Sub

I turned on the enableevents stuff. If you're changing another cell, you
probably don't want the code to fire again.

The Me. refers to the thing that we'er in. Since we'er in a worksheet module,
it refers to the worksheet owning the code.

(I also changed your if/then into a multiple line if/then. I just find that
easier to read when it's in that portion of code--but that's just my
preference.)

Job wrote:

Hello all,

I'm trying to use the workshee_change event to grab the value of the current
filter criteria1 and then paste that value into a particular cell. However,
it's not working. Anyone have ideas? Here's the code;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$H$991" Then
'Application.EnableEvents = False
If Target.Value = 10 Then
With ActiveWorkbook
If .AutoFilterMode Then
With .AutoFilter.Filters(1)
If .On Then c1 = .Criteria1
Range("F992").Value = c1
End With
End If
End With
End If
' Application.EnableEvents = True
End If
End Sub

Cheers,

Job

--

Dave Peterson