Check each sheet for Auto-filtering and switch it off

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

Hi everyone

This code works on one sheet, but I realise that I actually need it to
look at each sheet in my workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
Dim myRow As Range

Set mySht = Sheets("Sheet1")
Set myRow = mySht.Rows("1:1")

If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
ActiveWorkbook.Save
End If
End Sub


I played around with ( For each sheet ) but could not get the structure
correct.

My sheet array is "Jan" through to "Dec"

Also, I have a Timer control that is triggered after 5 mins of user
sheet inactivity which I would like this to be triggered just prior to
the workbook saving and closing...

As always
Heaps of thanks in advance..

Mick.
 
Hi everyone

This code works on one sheet, but I realise that I actually need it to
look at each sheet in my workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
Dim myRow As Range

Set mySht = Sheets("Sheet1")
Set myRow = mySht.Rows("1:1")

If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
ActiveWorkbook.Save
End If
End Sub

I played around with ( For each sheet ) but could not get the structure
correct.

My sheet array is "Jan" through to "Dec"

Also, I have a Timer control that is triggered after 5 mins of user
sheet inactivity which I would like this to be triggered just prior to
the workbook saving and closing...

As always
Heaps of thanks in advance..

Mick.
Click to expand...

Try this:
Dim mySht As Worksheet
Dim myRow As Range

for each mySht in ActiveWorkbook.WorkSheets
Set myRow = mySht.Rows("1:1")
If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
End If
Next mySht
ActiveWorkbook.Save
 
Dim mySht As Worksheet
Dim myRow As Range

for each mySht in ActiveWorkbook.WorkSheets
Set myRow = mySht.Rows("1:1")
If myRow.AutoFilter = True Then
myRow.AutoFilter
myRow.AutoFilter
End If
Next mySht
ActiveWorkbook.Save
Click to expand...
Hi

Thx for the pointer

It's works on te first sheet, then halts on the second sheet.

Think it has something to do with myRow not being specific enough to
cover all 12 sheets..

Thx again.
Mick
 
This was posted in another NG on Jan 27, 2012...

Sub TurnOffFilterMode()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.FilterMode Then
With wks.UsedRange
.AutoFilter '//turn it off
.AutoFilter '//reset the dropdowns
End With
End If 'wks.FilterMode
Next 'wks
End Sub
 
Sub test()
Dim mySht As Worksheet
Dim myRow As Range
For Each mySht In ActiveWorkbook.Worksheets
If mySht.AutoFilterMode Then
mySht.Range("A1").AutoFilter
End If
Next mySht
ActiveWorkbook.Save
End Sub


Gord
 
If you just want to switch the filter off, then this will do the trick:

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.AutoFilterMode = False
Next



CE



Den 09.02.2012 10:46, Vacuum Sealed skrev:
Click to expand...

As always

You guy's are amazing.

To all, thx heaps.

Cheers
Mick.
 
Back
Top