Macro and Pivot tables using a CUBE as data source
I'm using several pivot tables all driven from the same Cube. All tables are
using the same field to filter on [MAJOR TERMINAL CODE-NAME]. The task is to create a macro that will read the first filter, then copy that value to the other tables. Problem, the Cube field [MAJOR TERMINAL CODE-NAME] contains two columns, (one text, one numeric), but only one is visible. When recording the field with mouse clicks it clearly captures the two values in the macro code. However, when I try to use ActiveCell.PivotCell.Range.Value only the first column gets captured to my variable. Here is my code: pivotchoice = ActiveCell.PivotCell.Range.Value pivotcode = "[BOOKING AGENCY].[BOOKING AGENCY-MAJOR].&[" & pivotchoice & "]" ActiveSheet.PivotTables("Agent Total").PivotFields( _ "[BOOKING AGENCY].[BOOKING AGENCY-MAJOR].[MAJOR TERMINAL CODE-NAME]"). _ CurrentPageName = pivotcode Any idea how to capture both columns to the variable pivotchoice -- Ken |
All times are GMT +1. The time now is 10:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com