Sticking filter criteria in VBA

[picklefactory]

Hi folks
I have a workbook I'm trying to tweak. I have a number of form control buttons running macros on the title bar of the sheet simplifying a few very basic tasks just sorting or filtering based on single criteria only... all works fine, been using it for weeks now....until I try and add an additional filter for a different column and criteria. Currently, there was only one filter in place. This new filter is still only based on a single criteria in a single column, but no matter how I choose to action it, once it has functioned, I cannot get rid of it and it then seems to work as an additional criteria to my other filter. I'll try and explain more clearly. The first, original filter is simply filtering a range by customer name in a single column from a ComboBox in a user form that generates a list of customers, that works fine with no noticeable issues. I have a Reset function to reset the entire workbook back to initial opening condition..... also works fine. The new filter is simply an alternative option to the customer filter to filter any rows that contain "No WO" in a specific column, and is not intended to work with or as an additional criteria to the customer filter, simply a second, stand alone filter for a different column and criteria. I have tried umpteen methods of actioning it, a simple control button on the worksheet running a macro, a command button on a userform running from a textbox input, running it manually from the VBA window etc..... all of them operate the new filter perfectly, but it then seems to stick. I can reset the sheet and all goes back to WB opening condition (Unfiltered), at least visually, but if I then try and run the customer name filter, that then functions with the added criteria of the 2nd filter, so I can then only see rows containing the selected customer + "No WO" as a 2nd criteria. It's probably something dumb I'm missing, but it's been frying my head all afternoon. Can anyone see what I'm missing please?

Apologies for the ton of code here, but I don't know where the issue is, so not sure which bits to post.

This is the sheet unfiltered

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	SALES ORDER	CUST PO	PART NUMBER	PART DESCRIPTION	CUSTOMER	QTY	DUE DATE	STOCK	NO OF OPS	CURRENT OP	WORKS ORDER
3	13459	0	20759706	BRACKET	Castings PLC	84	30/05/18	71	1	1	211739
5	13447	CFC2 ENG-113029	BG4001.0	EDGE SENSOR JIG BOTTOM	Ocado Ltd	1	29/04/18	0	2		211746
6	13447	CFC2 ENG-113029	BG4001.1	EDGE SENSOR JIG TOP	Ocado Ltd	1	29/04/18	0	3		211747
8	13416	X22919	161728-46786	1890 TANK ID ADR CHUTE	Whale Tankers	1	01/05/18	0			No WO
10	13441	180416	WG.002	COOLANT PROTECTOR 750MM	Wogaard	10	30/04/18	0			No WO
14	10141	5500034847	347-10450	WA Fan Guard LH	JCB Manufacturing	20	01/05/18	0	7		211811
15	10142	5500182547	333-H0405	WA RH FAN GUARD	JCB Manufacturing	20	01/05/18	0	7		211812
19	13454	14354	667-2-30070-003	3 ASPECT BRACKET	Counterplas Ltd	2500	30/05/18	0	5		211816
20	13454	14354	667-2-30078-000	SIDE TO SIDE PLATES	Counterplas Ltd	500	30/05/18	0			No WO
21	13454	14354	667-2-30070-004	4 ASPECT BRACKET	Counterplas Ltd	350	30/05/18	0	5		211817
22	13454	14354	667-2-30070-002	2 ASPECT BRACKET	Counterplas Ltd	350	30/05/18	0	5	1	211818
24	10358	5500048951	320-06429	FLANGE EXHAUST	JCB Power Systems	200	30/04/18	200	3	2	211607
26	10143	5500034846	347-10322	WA Mounting Bracket	JCB Manufacturing	20	16/04/18	0			No WO
28	10632	RE36557	335-A0563	PRESSING FENDER MOUNT	JCB Heavy Products	25	21/05/18	0			No WO
30	13350	X20975	171521-55280	SUPPORT BAR SUZIE	Whale Tankers	1	12/03/18	0			No WO
31	13390	X21957	157222-45439	WATER FEED PIPE	Whale Tankers	1	09/04/18	0	9	4	211624
32	13390	X21957	157429-45482	VACUUM PUMP TS	Whale Tankers	1	09/04/18	0	6		211630
33	13390	X21957	168537-52199	SPIGOT-TUBE	Whale Tankers	1	09/04/18	0			No WO
34	13406	X22492	154072-40922	HYD BOOM SIDE ARM SUPPORT	Whale Tankers	1	14/04/18	0	3		211760
FAB

This is the sheet after the customer filter

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	SALES ORDER	CUST PO	PART NUMBER	PART DESCRIPTION	CUSTOMER	QTY	DUE DATE	STOCK	NO OF OPS	CURRENT OP	WORKS ORDER
8	13416	X22919	161728-46786	1890 TANK ID ADR CHUTE	Whale Tankers	1	01/05/18	0			No WO
30	13350	X20975	171521-55280	SUPPORT BAR SUZIE	Whale Tankers	1	12/03/18	0			No WO
31	13390	X21957	157222-45439	WATER FEED PIPE	Whale Tankers	1	09/04/18	0	9	4	211624
32	13390	X21957	157429-45482	VACUUM PUMP TS	Whale Tankers	1	09/04/18	0	6		211630
33	13390	X21957	168537-52199	SPIGOT-TUBE	Whale Tankers	1	09/04/18	0			No WO
34	13406	X22492	154072-40922	HYD BOOM SIDE ARM SUPPORT	Whale Tankers	1	14/04/18	0	3		211760
35	13406	X22492	154074-43468	EXTENDING ARM FAB	Whale Tankers	1	14/04/18	0	7	1	211761
36	13406	X22492	175491-55675	5" PIPEWORK	Whale Tankers	2	14/04/18	0	7		211633
37	13406	X22492	175783-55916	HIGH LIFT BOOM ARM	Whale Tankers	1	14/04/18	0	9		211762
38	13406	X22492	175787-55927	MOUNTING BRACKET	Whale Tankers	1	14/04/18	0	4		211765
FAB

This is the sheet after 'No WO' filter

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	SALES ORDER	CUST PO	PART NUMBER	PART DESCRIPTION	CUSTOMER	QTY	DUE DATE	STOCK	NO OF OPS	CURRENT OP	WORKS ORDER
8	13416	X22919	161728-46786	1890 TANK ID ADR CHUTE	Whale Tankers	1	01/05/18	0			No WO
10	13441	180416	WG.002	COOLANT PROTECTOR 750MM	Wogaard	10	30/04/18	0			No WO
20	13454	14354	667-2-30078-000	SIDE TO SIDE PLATES	Counterplas Ltd	500	30/05/18	0			No WO
26	10143	5500034846	347-10322	WA Mounting Bracket	JCB Manufacturing	20	16/04/18	0			No WO
28	10632	RE36557	335-A0563	PRESSING FENDER MOUNT	JCB Heavy Products	25	21/05/18	0			No WO
30	13350	X20975	171521-55280	SUPPORT BAR SUZIE	Whale Tankers	1	12/03/18	0			No WO
33	13390	X21957	168537-52199	SPIGOT-TUBE	Whale Tankers	1	09/04/18	0			No WO
47	13283	X19006 C/O	135075	SA REEL SPOOL 120MTR LWT	Whale Tankers	4	30/04/18	0			No WO
52	13434	X23406	156726-44528	WITTIG RFW 120 PUMP	Whale Tankers	1	01/05/18	0			No WO
56	13308	X19882	171524-55295	BRACKET SUZI SUPPORT	Whale Tankers	2	03/05/18	0			No WO
57	13308	X19882	171520-55279	OS PACKING SHIM	Whale Tankers	3	03/05/18	0			No WO
67	13465	X24562	145631	1300 DE-WATERING ASSY. DR	Whale Tankers	5	22/05/18	0			No WO
69	13445	X23769 C/O	135075	SA REEL SPOOL 120MTR LWT	Whale Tankers	5	30/05/18	0			No WO
FAB

This is the sheet when I then reset and clear all filters, then rerun the customer filter.... it also brings in the "No WO" filter too

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	SALES ORDER	CUST PO	PART NUMBER	PART DESCRIPTION	CUSTOMER	QTY	DUE DATE	STOCK	NO OF OPS	CURRENT OP	WORKS ORDER
8	13416	X22919	161728-46786	1890 TANK ID ADR CHUTE	Whale Tankers	1	01/05/18	0			No WO
30	13350	X20975	171521-55280	SUPPORT BAR SUZIE	Whale Tankers	1	12/03/18	0			No WO
33	13390	X21957	168537-52199	SPIGOT-TUBE	Whale Tankers	1	09/04/18	0			No WO
47	13283	X19006 C/O	135075	SA REEL SPOOL 120MTR LWT	Whale Tankers	4	30/04/18	0			No WO
52	13434	X23406	156726-44528	WITTIG RFW 120 PUMP	Whale Tankers	1	01/05/18	0			No WO
56	13308	X19882	171524-55295	BRACKET SUZI SUPPORT	Whale Tankers	2	03/05/18	0			No WO
57	13308	X19882	171520-55279	OS PACKING SHIM	Whale Tankers	3	03/05/18	0			No WO
67	13465	X24562	145631	1300 DE-WATERING ASSY. DR	Whale Tankers	5	22/05/18	0			No WO
69	13445	X23769 C/O	135075	SA REEL SPOOL 120MTR LWT	Whale Tankers	5	30/05/18	0			No WO
FAB

Worksheet Open Code

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    
    Dim x As Workbook

'## Open FAB workbook first:
    Set x = Workbooks.Open("X:\ORDER BOOK2\NEW ORDER BOOK\Order Book Fab Data.xls")
    
    'Copy/paste FAB data
    Application.Calculate
    Worksheets("FAB").Activate
    ActiveSheet.Cells.EntireRow.Hidden = False 'ENSURE ALL ROWS ARE UNHIDDEN INITIALLY
    Worksheets("DATA INPUT").Range("A2:L500").Copy
    Worksheets("FAB").Range("A2").PasteSpecial xlPasteValues
    Worksheets("FAB").Range("A2").Select
    BeginRow = 1 'START ROW
    EndRow = 500
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "HIDE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
     
     x.Close
   
    'Open MC shop workbook next
    Set x = Workbooks.Open("X:\ORDER BOOK2\NEW ORDER BOOK\Order Book MC Data.xls")
 
    'Copy/paste MC shop data
    Application.Calculate
    Worksheets("MACHINE SHOP").Activate
    ActiveSheet.Cells.EntireRow.Hidden = False 'ENSURE ALL ROWS ARE UNHIDDEN INITIALLY
    Worksheets("DATA INPUT MC SHOP").Range("A2:K500").Copy
    Worksheets("MACHINE SHOP").Range("A2").PasteSpecial xlPasteValues
    Worksheets("MACHINE SHOP").Range("A2").Select
    BeginRow = 1 'START ROW
    EndRow = 500
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "HIDE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    
    x.Close

   
    
    'Hide row/column letters/numbers
        Dim wsSheet As Worksheet
    For Each wsSheet In ThisWorkbook.Worksheets
        If Not wsSheet.Name = "Blank" Then
            wsSheet.Activate
            With ActiveWindow
                .DisplayHeadings = True
                .DisplayWorkbookTabs = True
                .DisplayHorizontalScrollBar = True
            End With
        End If
    Next wsSheet
    
    Application.ScreenUpdating = True
    Worksheets("FAB").Activate
    Worksheets("FAB").Range("A2").Select
    Call ClearClipboard
End Sub

Module 1

Sub DateSortFAB()
 Range("A2:L250").Sort key1:=Range("G2"), order1:=xlAscending, _
  Header:=xlNo
End Sub
Sub ValueSortFAB()
 Range("A2:L250").Sort key1:=Range("L2"), order1:=xlDescending, _
  Header:=xlNo
End Sub
Sub CustomerSortFAB()
 Range("A2:L250").Sort key1:=Range("E2"), order1:=xlAscending, _
  Header:=xlNo
End Sub
Sub FilterCustomerFAB()
UserForm1.Show
End Sub
Sub ResetFAB()
    Application.ScreenUpdating = False
    ActiveSheet.Cells.EntireRow.Hidden = False 'ENSURE ALL ROWS ARE UNHIDDEN INITIALLY
    Worksheets("DATA INPUT").Range("A2:L500").Copy
    Worksheets("FAB").Range("A2").PasteSpecial xlPasteValues
    Worksheets("FAB").Range("A1").Select
    BeginRow = 1 'START ROW
    EndRow = 500
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "HIDE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    ActiveSheet.Cells.EntireRow.Hidden = False 'ENSURE ALL ROWS ARE UNHIDDEN INITIALLY
    Worksheets("DATA INPUT").Range("A2:L500").Copy
    Worksheets("FAB").Range("A2").PasteSpecial xlPasteValues
    Worksheets("FAB").Range("A1").Select
    Application.CutCopyMode = False
    BeginRow = 1 'START ROW
    EndRow = 500
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "HIDE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    Application.ScreenUpdating = True
    
    Call ClearClipboard

End Sub

UserForm 1 code - this is the original filter that runs from the combobox

[CODEPrivate Sub CommandButton1_Click()
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:=ComboBox1.Value
UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=11, Criteria1:="No WO"
UserForm1.Hide
End Sub

Private Sub UserForm_Initialize()

Dim v, e
With Sheets("FAB").Range("E2:E500")
v = .Value
End With
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In v
If Not .exists(e) Then .Add e, Nothing
Next
If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
End Sub
[/CODE]

 

[Fluff]

Untested, but try

Private Sub CommandButton2_Click()
With ActiveSheet.ListObjects("Table1").Range
   If .FilterMode Then .ShowAllData
   .AutoFilter Field:=11, Criteria1:="No WO"
End With
UserForm1.Hide
End Sub

 

[picklefactory]

Thanks fluff. I've just got home now, but I'll try first thing tomorrow and report back

 

[Fluff]

Ok

 

[picklefactory]

Morning all
Hi Fluff...frayed knot....I get 'Run time error 438, Object doesn't support this property of method'. It hangs on the 'If .FilterMode Then' element.

 

[picklefactory]

I edited your code and got rid of the Run time error, but no change to the results, I'm afraid.

Private Sub CommandButton1_Click()
With ActiveSheet.ListObjects("Table1").Range
   If ActiveSheet.FilterMode Then .ShowAllData
   .AutoFilter Field:=5, Criteria1:=ComboBox1.Value
End With
   UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
With ActiveSheet.ListObjects("Table1").Range
   If ActiveSheet.FilterMode Then .ShowAllData
   .AutoFilter Field:=11, Criteria1:="No WO"
End With
UserForm1.Hide
End Sub

Private Sub UserForm_Initialize()
Dim v, e
With Sheets("FAB").Range("E2:E500")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
End Sub

 

[picklefactory]

In hindsight, and looking closer... the issue affects both filters in the same way. Which ever of the two filters gets actioned first then gets sucked into the 2nd filter. My Reset function is clearly not removing the filter criteria. The last sheet view I posted in my first post is the result of EITHER filter if they have both been run previously

 

[Fluff]

How about

Private Sub CommandButton2_Click()
With ActiveSheet.ListObjects("Table1").AutoFilter
   If .FilterMode Then .ShowAllData
   .Range.AutoFilter 11, "No WO"
End With
UserForm1.Hide
End Sub

 

[picklefactory]

That made a difference.....very promising, thank you.
I need to play a bit more...... this is where you shout at me... there's also a bunch of scattered rows hidden that contain information not to be displayed, your latest help un-hides those too, but I have code to re-hide them......they will be playing peek-a-boo (Hide, unhide, hide again), but hey ho, if it ends up right I'm happy.
Back in a bit

 

[Fluff]

Add

Application.ScreenUpdating = False

to the top of the code