VBA - Subscript Out of Range

Massmann05

New Member
Joined
Mar 22, 2017
Messages
7
Good Afternoon, been racking my brain on this one for awhile and cannot figure it out. I keep getting a subscript out of range on the second line of code listed below. When I run this code on a regular table it works great, but when ran on a pivot table it wont work. Any help would be greatly appreciated. Thank-you!

Code:
Sheets("~Calcs").Select
ActiveSheet.ListObjects("ptCSV1").Range.AutoFilter Field:=8, Criteria1:="<>0"
Call CSV
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A pivot table is not a ListObject hence the subscript error.

I am not 100% sure, how to get the autofilter to work correctly but the code below errors at the autofilter line only because the filter in incomplete...

Code:
    Dim pivot As PivotTable
    Set pivot = ActiveSheet.PivotTables("ptCSV1")
    pivot.ColumnRange.AutoFilter

I hope this gets you closer...
 

Massmann05

New Member
Joined
Mar 22, 2017
Messages
7
Thanks for the response, I tried the above with no luck but figured out why. I was trying to apply a filter to a value column in the pt which fo. The below now works, not pretty but works.

Code:
Sheets("~Calcs").Select
ActiveSheet.PivotTables("ptCSV1").PivotFields("Cost").PivotFilters.Add Type:= _
xlValueDoesNotEqual, DataField:=ActiveSheet.PivotTables("ptCSV1").PivotFields _
("Sum of Qty"), Value1:=0
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks for the feedback. I am glad you have it working, pretty or not!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,522
Messages
5,523,363
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top