Filter an Excel OLAP PivotTable with a Cell Value Using VBA
Question details
The user needs to filter an Excel OLAP PivotTable using a value from a worksheet cell in VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A macro filters an OLAP PivotField successfully with a hard-coded member but needs to use the value in cell D3 instead.
- Observed behavior
- Assigning the cell value directly to CurrentPage produces an Unable to set the CurrentPage property error.
Confirm that the target field is an OLAP report filter and identify its exact member unique-name format. Check that cell D3 contains the member key, including any leading zeros, rather than only the displayed member caption.
Assign the OLAP member unique name to CurrentPageName
Use this method when an OLAP report filter must select a member based on a worksheet cell.
OLAP PivotFields use member unique names. A raw cell value or display caption generally cannot be assigned directly to CurrentPage.
In Excel, open Developer > Visual Basic and locate the macro that filters the PivotTable. Ensure its PF variable refers to the target OLAP PivotField.
Replace the CurrentPage assignment with: Str = Worksheets("Double Entries").Range("D3").Value PF.ClearAllFilters PF.CurrentPageName = "[#GL_LineItems].[Accounting Document Number].&[" & Str & "]". Adjust the hierarchy and member syntax to match the unique names supplied by your OLAP source.
Return to Excel and run the procedure from Developer > Macros. Check that the PivotTable report filter selects the member whose key matches D3.

Work with Excel files in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday workbook tasks between applications. It does not fix Excel's OLAP CurrentPage error; check compatibility before relying on OLAP connections or VBA macros.

Frequently Asked Questions
Why does assigning D3 directly to CurrentPage fail?
The OLAP field expects a member identified in the form used by its cube. Construct the member unique name from the key in D3 and assign it to PF.CurrentPageName instead.
Does D3 need to contain the member caption or the key?
For the example's unique-name format, D3 must contain the exact member key. If the key has leading zeros, format the cell as text and enter the complete key.
What should I change if my OLAP field has a different name?
Replace the hierarchy portion of the CurrentPageName string with the exact hierarchy and member unique-name syntax from your OLAP source. Also make sure PF refers to the intended report filter field.
Will ClearAllFilters affect other selections?
Yes. PF.ClearAllFilters clears existing filters on that PivotField before the macro selects the new member. Review the field's existing filters before running the macro.




