Top 10 filter code

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
192
Office Version
  1. 2010
hello,
i have a code from a control page it will allow you to select a name on a control page from a drop down box and filter another page for results just for the person you selected. code below. I added a 2nd code once you got to the page of results for the selected person to filter the results by their top ten customers. it appears that code is filtering the page for everyones top ten customers not just the person selected and in turn i now get an error when i select the first code. can you see what i am doing wrong (2nd code listed after 1st)
1)
Code:
Sub OfficerFilter()


Dim dd As DropDown
Dim offName As String
Dim i As Long




'This macro is run when the user makes a change to the officer drop down on the control page


'Go to tab Individual Results & Set the Cursor
    Sheets("Results By Customer").Visible = True
    Sheets("Results By Customer").Select
    Range("K7").Select


'Clear any existing filters
    With ActiveSheet
      If .FilterMode Then
        ActiveSheet.ShowAllData
      End If
    End With


'Dynamically filter tab Individual Results based on value selected on the Individual Results page (stored in A5 via formula)
   'Refresh the advanced filter on based on the returned value above (do not go to that tab yet)
    Sheets("Results By Customer").Range("A7:AK20000").Select
    Selection.AutoFilter Field:=8, Criteria1:=Sheets("Results By Customer").Range("A4").Value
    
    Range("K7").Select


    
    'Hide Columns 1-3
    'Columns("A:C").Select
    'Selection.EntireColumn.Hidden = True

End Sub

2)
Code:
Sub top10Filter()
'
' top10Filter Macro
'
Dim lo As ListObject
Dim iCol As Long


Set lo = ActiveSheet.ListObjects(1)


'Set filter field
  iCol = lo.ListColumns("% Change").Index


'Apply filters to a column (field)
  lo.Range.AutoFilter Field:=iCol, Criteria1:="37", Operator:=xlTop10Items
  
End Sub
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
is that the correct active sheet
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top