PDA

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



rajkumar
09-11-2009, 05:43 AM
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.


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


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 : pray2:

nst1107
09-11-2009, 06:50 AM
Try this:
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

rajkumar
09-11-2009, 08:46 AM
Superb,
It's Working - Thanks a ton
:clap: :friends: :beerchug: