Autofilter (on/off)

[MJKelly]

Hi,

Can you help me with the following code. I want certain worksheets to
have an autofilter turned on. If there is already an autofilter on I
want it to stay on. Whatever I do, it switches on to off. The
username part is just so that the macro runs only if I am using the
spreadsheet.

Private Sub Workbook_Open()

Dim strUN As String
strUN = Application.UserName
Dim WkSht As Worksheet

Application.Calculation = xlCalculationAutomatic

If strUN = "matt" Then

For Each WkSht In ThisWorkbook.Worksheets
If Range("A3").Value = "Ref" Then
If Range("A3").AutoFilter = False Then
Application.AutoFilter = True
End If
End If

Next WkSht
End If

End Sub

many thanks,
Matt

 

[Norman Jones]

Hi Mj,

Try something like:

'========>>
Private Sub Workbook_Open()

Dim strUN As String
strUN = Application.UserName
Dim WkSht As Worksheet

Application.Calculation = xlCalculationAutomatic

If strUN = "matt" Then
For Each WkSht In ThisWorkbook.Worksheets
With WkSht
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
End With
Next WkSht
End If

End Sub
'<<========