VBA & Macro Problems

Filter an Excel OLAP PivotTable with a Cell Value Using VBA

Khadija KhanKhadija Khan Nov 18, 2026 868 views

Question details

The user needs to filter an Excel OLAP PivotTable using a value from a worksheet cell in VBA.

Filter an Excel OLAP PivotTable with a Cell Value Using 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA procedure

In Excel, open Developer > Visual Basic and locate the macro that filters the PivotTable. Ensure its PF variable refers to the target OLAP PivotField.

2
Build and assign the unique name

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.

3
Run and verify the filter

Return to Excel and run the procedure from Developer > Macros. Check that the PivotTable report filter selects the member whose key matches D3.

Assign the OLAP member unique name to CurrentPageName
Check the member key: The cell value must exactly match the OLAP member key. Store keys with leading zeros as text when needed. ClearAllFilters removes existing filters on the field.
Free Microsoft Office alternative

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.

Open and edit spreadsheets in a familiar interfaceWork with Microsoft Excel XLSX and XLSM formatsReview and filter spreadsheet data for everyday analysisUse a free, lightweight office suite for common document tasks
microsoft office alternative - wps office

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.