PivotTable Filters in VBA

[Colin Macleod]

I'm having a problem setting a page filter. The code is as follows. This seems to work fine if the field contains text but I get an error on the last line if the field contains numerical data. Any help would be very gratefully received. Many Thanks

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Actual Cost")
.Orientation = xlPageField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable3").PivotFields("Actual Cost").ClearAllFilters

ActiveSheet.PivotTables("PivotTable3").PivotFields("Actual Cost").CurrentPage = "0"

 

[Hui]

Correct
Pivot tables take the inputs as Text even if it is a numeric field

eg:
ActiveSheet.PivotTables("PivotTable3").PivotFields("Actual Cost").CurrentPage = Cstr(0)
or
ActiveSheet.PivotTables("PivotTable3").PivotFields("Actual Cost").CurrentPage = Cstr(myVariable)

 

[NARAYANK991]

Hi Colin ,

I have tried your code in a workbook , without getting any error.

If you see the uploaded file , the code is in Module2 , in a procedure tst ; running it produces the correct result , without any error.

If you can upload your file , it may be easier to see what the real problem is.

Narayan

 

[Colin Macleod]

Thanks for your replies. I tried using the CStr function but still had the same error. Narayan - I think your uploaded file only works if the page filter value is set to something other than zero. I think the easiest way is for me is to add an extra text column to the database using the if function. Colin