picklefactory
Well-known Member
- Joined
- Jan 28, 2005
- Messages
- 506
- Office Version
- 365
- Platform
- Windows
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
This is the sheet after the customer filter
This is the sheet after 'No WO' filter
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
Worksheet Open Code
Module 1
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]
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
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 SubModule 1
Code:
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 SubUserForm 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]
