Auto Filter of range class failed

[ben_sorensen]

Hello I keep getting an error that I can't figure out why, I have been racking my brain for hours. I keep getting the auto filter of range class failed and all spelling is correct and there shouldn't be any problems. Here is my code.

Sub SplitCodeFilterSheet()
Dim wswb As StringDim wssh As String
wswb = ActiveWorkbook.Namewssh = ActiveSheet.Name

vcolumn = InputBox("Which column would you like to match items", "Match this")
Columns(vcolumn).CopySheets.AddActiveSheet.Name = ("_Summary")range("A1").PasteSpecialColumns("A").RemoveDuplicates Columns:=1, Header:=xlYes
vcounter = range("A" & Rows.Count).End(xlUp).Row
For i = 3 To vcounter
    vfilter = Sheets("_Summary").Cells(i, 1)    ActiveWorkbook.Sheets(wssh).Activate    ActiveSheet.Cells(i, vcolumn).Select       ActiveSheet.AutoFilterMode = False   ActiveSheet.Columns.AutoFilter field:=Columns(vcolumn).Column, Criteria1:=vfilter    Cells.Copy    Workbooks.Add    range("A2").PasteSpecial    If vfilter <> "" Then        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Reports\" & vfilter                Else                 ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Reports\_Blank"             End If        ActiveWorkbook.Close        Workbooks(wswb).Activate    Next i
Sheets("_Summary").Delete

    



End Sub

Any help would be most apprciated

Best

 

[ben_sorensen]

Apologies the code there looks sloppy:

Sub SplitCodeFilterSheet()


Dim wswb As String
Dim wssh As String


wswb = ActiveWorkbook.Name
wssh = ActiveSheet.Name




vcolumn = InputBox("Which column would you like to match items", "Match this")


Columns(vcolumn).Copy
Sheets.Add
ActiveSheet.Name = ("_Summary")
range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes


vcounter = range("A" & Rows.Count).End(xlUp).Row


For i = 3 To vcounter


    vfilter = Sheets("_Summary").Cells(i, 1)
    ActiveWorkbook.Sheets(wssh).Activate
    ActiveSheet.Cells(i, vcolumn).Select
    
   ActiveSheet.AutoFilterMode = False
   ActiveSheet.Columns.AutoFilter field:=Columns(vcolumn).Column, Criteria1:=vfilter
    Cells.Copy
    Workbooks.Add
    range("A2").PasteSpecial
    If vfilter <> "" Then
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Reports" & vfilter
        
        Else
        
         ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Reports\_Blank"
         
    End If
    
    ActiveWorkbook.Close
    
    Workbooks(wswb).Activate
    
Next i


Sheets("_Summary").Delete




    








End Sub

 

[Fluff]

Your code works for me.
Do you get the error on the 1st run through, or do some workbooks get created?

 

[ben_sorensen]

So it goes through the process of creating the other worksheet, pasting all that info and removing duplicates from those pasted values, then it pops an error for the runtime error. When I tell it to debug it highlights the filter line

 

[Fluff]

Any merged cells?
Is the sheet protected?

 

[ben_sorensen]

No none of that, the info that I am copying is in column J and it copies to the other page in column A then it uses those values to filter on column J and that is where the error is coming in. Column J I had a vlookup that I pasted special values to hard code those in thinking that may be the issue, but that isn't so I am not sure where I am getting an error.

 

[Fluff]

Is your data in a proper table?

 

[ben_sorensen]

Yes it is in table form, do you think that may be the issue?

 

[ben_sorensen]

Yep that was the issue

 

[Fluff]

Have you got it working?