Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Filter rows on sheet 2 when a certain option is chosen on sheet 1
I'm in excel 2003.
I have two worksheets in my excel workbook, "home" and "Data". In the "home" sheet I have a combo box asking the user to choose one of 4 stages of operation. The combo box is related to cell A26 in the “home” sheet. In the " Data " sheet I have data from row 7 - 120 in column F to V. Column Headers are in row 6. Based on that choice that the user makes in the combo box in the "home" sheet, certain rows in the " Data " sheet should 'disappear from view' and the best way that I can think to do this is with Autofilter. For example, if they choose “stage 1” in the combo box, then all rows that are (in column G) labeled as anything else other than “stage 1” will disappear. That is the theory, but I don't seem able to actually make it happen. My code seems to work as soon as there is a change in the combo box and choose the correct cells for the autofilter, but then it doesn’t actually filter anything! Anyone able to help out by letting me know where the errors are in my code below, or providing a different code in order to accomplish my task? Am I possibly using the wrong field number? Code says basically: - unprotect the worksheet, - remove filters (in case they were already there), - based on what the user chooses, add filters and filter the rows required in the “Data” sheet, - select a cell in the "home" sheet Code is as follows: Private Sub ComboBox1_Change() Application.ScreenUpdating = False Worksheets("Data ").Unprotect ("password") Sheets("Data ").AutoFilterMode = False Dim sel1 As Variant Set sel1 = Worksheets("home").Cells(1, 26) Sheets("Data ").Range("F6:V6").AutoFilter Field:=2, Criteria1:=sel1 Worksheets("Data ").Protect Password:="password", DrawingObjects:=True, UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True Worksheets("home").Cells(24, 10).Select End Sub |
#2
|
|||
|
|||
Quote:
I have attached the worksheet so anyone can try play with the code for real. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 can not filter / sort in sheet with 657,000 rows | Excel Discussion (Misc queries) | |||
Filter Range on Sheet B Based on List on Sheet A | Excel Discussion (Misc queries) | |||
How to paste data over the hidden rows in a sheet with a filter | Excel Discussion (Misc queries) | |||
'Copy to' Advance Filter depend only on sheet ID not start sheet | Excel Worksheet Functions | |||
Opening a workbook to a chosen sheet? | Excel Discussion (Misc queries) |