Change Pivot Table Captions

[mth]

Hello everybody!

I have problem with changing multiple captions on pivot table.

I am useing olap cube and I want to update all my "product code" captions on pivot table. Can it be done with VBA code?

I have old code-new code table and i want to replace the old code captions with new ones, copy paste does not work on pivot tables, and formulas are allso not available, but is there a way to change the captions with VBA code?

 

[Andrew Poulsom]

You can overtype the entries, so presumably you could loop around the cells containing product codes in your pivot table, use the VLookup WorksheetFunction to get the new code and set the cell's value to that. It may not be possible with an OLAP source though - I don't have one to test.

 

[mth]

vlookup is my friend, but it does not work on olap cubes, since you can not enter formulas to olap cube rows, you can only retype the caption. is there a way to change the caption with vba script?

 

[Andrew Poulsom]

I didn't say put a formula in the pivot table. Use VLookup in VBA to assign the new value to a variable. Then assign the value of that variable to the cell whose value you looked up.

 

[mth]

I have problem with assigning the value to pivot table caption, cant find the right code for it. I used vlookup to get the "new product code" to row B. So now I have pivot table on row A with old codes, and excel table with new codes and I need to copy value from B2 to A2 caption, B3 to A3 etc. I tried to record my macro using relative referenes and thats what I got:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "NEW_CODE"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Toode].[No].[No]"). _
PivotItems("[Toode].[No].&[OLD_CODE]").Caption = _
"NEW_CODE"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

What should I change here if I want to change all the captions?

 

[Andrew Poulsom]

You would loop through the PivotItems collection look up the caption and replace it.

 

[mth]

Thank you for your help! But I dont know how to do it.

 

[Andrew Poulsom]

If you were to put a VLOOKUP formula in a cell outside the pivot table what would it look like? And what would the Caption that you are looking up be? I have to ask these questions because I am unfamiliar with OLAP pivot tables.