Run-time error 1004 - Autofilter Method of Range Class Failed

Question

When I run my vba, I get the

Run-time error'1004; AutoFilter method of Range Class failed

I tried changing the worksheet as sheet 1 as it was recommended in a previous answer and it did not work

Sub Split_Data_in_workbooks()

Application.ScreenUpdating = False

Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("Working File")

Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Setting")

Dim nwb As Workbook
Dim nsh As Worksheet

''''''Get unique districts


setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("b:b").Copy setting_Sh.Range("A1")

setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes

Dim I As Integer

For I = 2 To Application.CountA(setting_Sh.Range("A:A"))

   data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & I).Value

   Set nwb = Workbooks.Add
   Set nsh = nwb.Sheets(1)

   data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")

   nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & 
I).Value & ".xlsx"
   nwb.Close False
   data_sh.AutoFilterMode = False
Next I

setting_Sh.Range("A:A").Clear

MsgBox "Done"

End Sub

The code is supposed to take each district's information (which could be multiple lines), which from one workbook and save it into separate workbooks and save them in a designated file. The code will not run pass this line

data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & I).Value

FFFffff FFFffff · Accepted Answer · 2019-05-29T19:43:11

You are probably passing incorrect arguments to the data_sh.UsedRange.AutoFilter method. First argument must not be larger than the number of fields - try 1 instead of 2. Description of the second argument from the documentation:

The criteria (a string; for example, "101"). Use "=" to find blank fields, "<>" to find non-blank fields, and "><" to select (No Data) fields in data types. If this argument is omitted, the criteria is All. If Operator is xlTop10Items, Criteria1 specifies the number of items (for example, "10").

Try this line and then play with different arguments' values. Make sure you know what argument values are you passing to the function:

data_sh.UsedRange.AutoFilter 1

Run-time error 1004 - Autofilter Method of Range Class Failed

1 Answers