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.

On Feb 9, 11:46*am, Vacuum Sealed <noodn...@gmail.com> wrote:
> 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.

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

On 9/02/2012 9:47 PM, M.Abusido wrote:
> 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
>
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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

On Thu, 09 Feb 2012 23:55:54 +1100, Vacuum Sealed <(E-Mail Removed)>
wrote:

>On 9/02/2012 9:47 PM, M.Abusido wrote:
>> 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
>>
>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

On 10/02/2012 3:18 AM, Charlotte E. wrote:
> 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:
>> 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.

As always

You guy's are amazing.

To all, thx heaps.

Cheers
Mick.