Set a value for a field in a Power Pivot Table

Hi everyone, I am trying to set a specific value in a field of a power pivot table, using VBA. I discovered that working with a pivot table and a power pivot table require different approaches. ...

excel

Macros and VBA

LeonPavesic

Silver Contributor

Oct 16, 2023

Hi NateP86,

The issue is likely with the way you are trying to set the value for the field.

You can try this solution for setting a value for a field in a Power Pivot table using VBA:

Sub SetFieldValueInPowerPivotTable(pivotTable As PivotTable, fieldName As String, fieldValue As String)

    ' Get the CubeField object for the specified field.
    Dim field As CubeField
    Set field = pivotTable.CubeFields(fieldName)

    ' Clear any existing filter on the field.
    field.ClearManualFilter

    ' Set the CurrentPage property of the field to the specified value.
    field.CurrentPage = fieldValue

End Sub

This macro is the same as the one in my previous response, but I have renamed it to SetFieldValueInPowerPivotTable to make it more clear what it does.

To use this macro, simply pass in the following arguments:

pivotTable: The PivotTable object that you want to set the value in.
fieldName: The name of the field that you want to set the value in.
fieldValue: The value that you want to set the field to.

For example, the following code would set the value of the WeekNo field in the WTD EMEA Power Pivot table to the value 41:

Dim pivotTable As PivotTable
Set pivotTable = ThisWorkbook.Sheets("EMEA Channels").PivotTables("WTD EMEA")

Dim fieldName As String
fieldName = "[Calendar].[WeekNo]"

Dim fieldValue As String
fieldValue = "41"

Call SetFieldValueInPowerPivotTable(pivotTable, fieldName, fieldValue)

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

NateP86

Copper Contributor

Oct 16, 2023

Hello LeonPavesic,

first of all, many thanks for your reply.

I tried, as per the attachment, the script you sent, hope properly 🙂

It seems that CurrentPage method is not accepted for power pivot tables.

Does it make sense for you?

Maybe it's something that fits well with standard pivot table, what do you think?

I happens the same just changing the 20th line with your suggestion.

Sub Filter()

    Dim wtdEMEA As PivotTable
    Dim filterValue As Long
    Dim fieldWeek As CubeField
    
    ' I read the power pivot table
    Set wtdEMEA = ThisWorkbook.Sheets("EMEA Channels").PivotTables("WTD EMEA")
    
    ' I take the value to filter in the power pivot table (es. 41)
    filterValue = Worksheets("Cover").Range("G42").Value
    
    ' I take the field
    Set fieldWeek = wtdEMEA.CubeFields("[Calendar].[WeekNo]")
    
    'I clear the field
    fieldWeek.ClearManualFilter
    
    'I try to set the field with the value taken before in the cell G42
    fieldWeek.CurrentPage = filterValue
       
End Sub

Please let me know what do you think.

Thank you.

Nate

Forum Discussion

Set a value for a field in a Power Pivot Table

Resources