Macro that would filter pivot table top 10 items

**weecha** · 05-29-2013, 01:14 PM

Hi, I am running into the "Run time error '1004': Autofilter method of Range Class failed" when i tried to run the macro for the auto filter that i recorded . It is a relatively simple macro, not sure why it isnt working...

Code:

Sub TopCus()
'
' TopCus Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'

ActiveSheet.Range("$A$4:$K$1112").AutoFilter Field:=11, Criteria1:="10", _
Operator:=xlTop10Items

End Sub

Please help!!! Thanks!!
W

**GeneralDisarray** · 05-29-2013, 01:21 PM

well, are you using the same exact range for the data? Do you need to alter $A$4:$K$1112 ?

--

In general, you would be wise to create a named range or table object for the table you want to filter in this way. you can then include the range in the macro by name (which is good for future runs when the row count is different). Example below:

ActiveSheet.ListObjects("TABLE_NAME").Range.AutoFilter Field:=1, Criteria1:= _
"10", Operator:=xlTop10Items

**weecha** · 05-29-2013, 01:26 PM

Hi, sorry i am very new in this. Did you mean defining the pivot table in the worksheet?

And yes, the range is the same..

Thanks,
W

**GeneralDisarray** · 05-29-2013, 01:28 PM

can you upload an example? pretty sure i can help you out

--

yes, you can access a pivot table by name also. Here's an example i just recorded from a pivot table of my own. You can edit this to reflect your information

ActiveSheet.PivotTables("PivotTable1").PivotFields("Row_label_name").PivotFilters. _
Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("CustomerFieldName"), Value1:=10