changing pivot table Report Filter with VBA

0
votes

I have a VBA code that changes the a pivot table Report Filter based on a cell.

It all works fine with inputs that are alphanumeric. When the input is a number, I get an error.

Here is the code:

Sub ProjSelect_PivotsUpdate()
    Dim Selected_Proj

    Selected_Proj = Worksheets("Parameters").Range("SelectedProj")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").CurrentPage = _
    Selected_Proj
End Sub

Here is the error code:

Run-time error '1004':

Application-defined or object-defined error.

2
excelpivot-tablevba

2 Answers

1
votes

trimming the value would solve your problem.

Sub ProjSelect_PivotsUpdate()
    Dim Selected_Proj

    Selected_Proj = Worksheets("Parameters").Range("SelectedProj")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").CurrentPage =  Trim(Selected_Proj)
End Sub
0
votes

You need to set the Range("SelectedProj") format to string,or just simple add ' before you input a number.