VBA:错误方法对象"_Worksheet"的"AutoFilterMode"失败 [英] Vba: error Method 'AutoFilterMode' of object '_Worksheet' failed

问题描述

在您的帮助下，我有以下脚本:在工作表"ANAF CONTURI"(我在其中输入数据)基于G列(="DA")的条件，它将结果过滤并复制到名为"ANAF"的现有工作表中BANCI"，范围从C2开始.

With your help i have this script that: in sheet "ANAF CONTURI" (where i input data) based on a criteria on column G (="DA") it filters and copies the results into a existing sheet named "ANAF BANCI" on a range starting from C2.

在我输入数据的地方，我还做了一个按钮来执行宏(它在A1和A2标头上).

Where i input the data i made also a button to execute the macro (its on A1, and on A2 headers).

尝试删除过滤器时出现此错误:

I get this error when it's trying to remove the filters:

运行时错误'-2147417848(80010108)':对象"_Worksheet"的方法"AutoFilterMode"失败.

，然后崩溃.(在某些例外情况下，它可以正常工作.没有任何修改)

and then crashes. (on some exceptions it works fine..without any modifications)

btw:sheet8实际上是"ANAF CONTURI"

btw: sheet8 is actually "ANAF CONTURI"

这是我的代码，请提供帮助:

Private Sub CommandButton1_Click()
' TRIAZA INFORMATIA INTRODUSA SI O INAINTEAZA SPRE PRELUCRARE IN ANAF BANCI
Sheet8.Unprotect Password:="online07"


Sheets("ANAF BANCI").Range("C2:H1000").ClearContents


    With Sheets("ANAF CONTURI") '<--| reference your sheet

        With .Range("G2", .Cells(.Rows.Count, 1).End(xlUp)) '<--| reference its column "A:G" range from row 1 down to column "A" last not empty row
            .AutoFilter Field:=7, Criteria1:="Da" ''<--| filter referenced range on its 7th column (i.e. column "G") with "Da" values
            If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then .Resize(.Rows.Count - 1, 6).Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("ANAF BANCI").Range("C2")
        End With
        .AutoFilterMode = False
    End With

    Sheet8.Range("A3:F20000").Locked = False
    Sheet8.Protect Password:="online07"

End Sub

推荐答案

我添加了此内容，而不是.AutoFilterMode = False，它似乎可以使用到现在:

I added this instead of .AutoFilterMode = False and it seems to work till now:

       If (Sheets("ANAF CONTURI").AutoFilterMode And Sheets("ANAF CONTURI").FilterMode) Or Sheets("ANAF CONTURI").FilterMode Then
  Sheets("ANAF CONTURI").ShowAllData
  End If

这篇关于VBA:错误方法对象"_Worksheet"的"AutoFilterMode"失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！