Filter Macro Error

[Patriot2879]

Hi please hope you can help, I have the code below where I have a command button which I have renamed to FilterData, then I have created a macro and assigned it to the FilterData button, but it doesn't work, the 2 lines below gets highlighted in yellow, I have also attached the whole code at bottom, I have a work issue sheet with all the data then I want this filtered into the sheet called filter but its not working, I hope you can help, I also have a combobox1 where the name gets copied into cell C% in filter, then this is copied into work issued cell M2 with a formula =Filter!C5, I hope I have explained this ok and hope you can help.

    Sheets("Work Issue").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Work Issue").Range("M1:N2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True

Sub FilterData()
    Sheets("Filter").Select
    Range("B10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    
    Sheets("Work Issue").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Work Issue").Range("M1:N2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True
    Columns.AutoFit
    Range("B10").Select
End Sub

 

[Yongle]

I have tested your VBA - it works
- table inserted on sheet "Work Issue"
- advanced filter criteria entered in cells M1:N2
- filtered data was correctly copied to sheet "Filter" beginning at cell B10

Do you get a message when the code fails?
eg Application-defined or object-defined error would tell you that Table1 does not exist - is Table1 the correct name for the table?

 

[Patriot2879]

I t understand why I am getting the error then, I have tried to assign a macro to a command button in the userform is this correct or do I put the code into the command button?

 

[Yongle]

What are you trying to achieve?

If you trying to make values selected from combobox on the userform change the value in M2 and/or N2 (and the userform is correctly changing those values) then try this code behind command button on the userform
(amend to match your command button name)

Private Sub [COLOR=#ff0000]CommandButton1[/COLOR]_Click()
    Unload Me
    Call FilterData
End Sub

 

[Patriot2879]

Hi sorry I am really stuck on this, what i am trying to do is once a name is selected in combobox1 in the userform, this will copy the name selected into cell C5 in the 'Filter' sheet, and when I click my command button which is called 'FilterData' this will filter out the name in the 'work issue sheet' and put the data into the 'filter' sheet

 

[Yongle]

The userform command button (named FilterData) should do the following
1. update C5 (which drives M2 on other sheet with formula =Filter!C5)
2. unload the form
3. filter the data

Userform code

Private Sub [I]FilterData[/I]_Click()
    Sheets("Filter").Range("C5") = ComboBox1.Value
    Unload Me
 'your previous code   
    Sheets("Filter").Select
    Range("B10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    
    Sheets("Work Issue").Range("[COLOR=#ff0000]Table1[/COLOR][#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Work Issue").Range("M1:N2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True
    Columns.AutoFit
    Range("B10").Select
End Sub

 

[Patriot2879]

hi do I copy all of the code into it? or just the code above my previous code?

 

[Patriot2879]

Hi sorry just tried this code unfortunately this didn't work either. sorry to be a pain

 

[Yongle]

I think you are confusing me
Have you named the command button FilterData or changed its caption to FilterData?
- they are NOT the same thing
I think you meant to say that you have changed the caption

Right click on the command button that you created on your userform \ click on View Code \ paste the code below into the procedure that is generated automatically

    Sheets("Filter").Range("C5") = ComboBox1.Value
    Unload Me
 'your previous code   
    Sheets("Filter").Select
    Range("B10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    
    Sheets("Work Issue").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Work Issue").Range("M1:N2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True
    Columns.AutoFit
    Range("B10").Select

I will not be back online for about 6 hours now

 

[Patriot2879]

HI I have renamed the command button to FilterData but i am still getting the below code coming up in yellow

    Sheets("Work Issue").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Work Issue").Range("M1:N2"), CopyToRange:=Sheets("Filter").Range("B10"), Unique:=True