Solved: Loop through All Worksheets to remove Autofiler & Freeze Pans before close

[rajkumar]

Hi Experts,

I need help in removing autofilter and freeze pan beforeclosing the workbook from all worksheets present in the workbook.

Currently i am using this code in the ThisWorkbook Before close to autosave the workbook.

[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("MIF BASE").AutoFilterMode Then
Worksheets("MIF BASE").AutoFilterMode = False
End If
Sheets("MIF BASE").Select
If ActiveWindow.FreezePanes = True Then
ActiveWindow.FreezePanes = False
End If
If Not ThisWorkbook.Saved Then
ThisWorkbook.Save
End If
End Sub
[/VBA]

Since it is shared by many users, they put autofilter or freeze pan and send to me. I had to manually remove autofilter and freeze pans everytime. when i try to consolidate data.

I have attached the sample xls herewith.

Kindly help

Raj

[nst1107]

Try this:[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
For Each ws In Worksheets
ws.AutoFilterMode = False
ws.Activate
ActiveWindow.FreezePanes = False
Next
If Not ThisWorkbook.Saved Then
ThisWorkbook.Save
End If
End Sub

[/vba]

[rajkumar]

Superb,
It's Working - Thanks a ton