How to remove AutoFilter of all worksheets in multiple Excel workbooks?

Pocket

It’s hard to remove manually AutoFilter of worksheets in multiple workbooks. I’d like to describe how to remove it with VBA.

Option Explicit

Sub RemoveAutoFilter()
    Dim Wb      As Workbook
    Dim Sh      As Worksheet
    Dim myPath  As Variant
    Dim i       As Long
    Application.ScreenUpdating = False
    myPath = Application.GetOpenFilename(FileFilter:="Microsoft Excel Book,*.xls?", MultiSelect:=True)
    If TypeName(myPath) = "Boolean" Then Exit Sub
    For i = LBound(myPath) To UBound(myPath)
        Set Wb = Workbooks.Open(myPath(i))
        For Each Sh In Wb.Worksheets
            If Sh.AutoFilterMode Then
                Sh.AutoFilterMode = False
            End If
        Next Sh
        Wb.Save
        Wb.Close
    Next i
    Application.ScreenUpdating = True
End Sub

Reference:
Application.GetOpenFilename Method (Excel)

Pocket

投稿者: admin

趣味:写真撮影とデータベース. カメラ:TOYO FIELD, Hasselblad 500C/M, Leica M6. SQL Server 2008 R2, MySQL, Microsoft Access.

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です