Pivot Table macros are not working in powerpivot

[nataly]

Hello,
I have a few macros that work well in pivot tables in Excel 2013. For instance they adjust all titles in the pivot table, or set all value items to sum - simple but helpful macros like that. Macros are found on internet or books and adjusted to make them work for my situation.
Using pivot table macros "as is" in powerpivot gives an error message, produces unwanted results, or crashes excel.
Is there an easy fix to make pivot table macros work for powerpivot as well?
Thanks,
Nataly

 

[tjdurkin]

Hi nataly,
The object model for powerpivot pivots is different than the regular pivot model. These macros will likely need to be updated for the PowerPivot 2013 object model. Putting this in the VBA Forum (Excel Questions) may get you a reply from someone who wants to take a crack at playing with the Powerpivot objects. If you don't get a response, post a workbook with the macros here and I'll take a look.

 

[nataly]

Hi tjdurkin,
Thanks for the reply! How do I attach a workbook to my post?
Here are a few of my favorite pivot table macros that I would like to work for powerpivot.
+++++++
Sub AdjustAllPivotDataFeildTitles()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
If pt Is Nothing Then
MsgBox "Cursor is not in PT"
Exit Sub
End If
For Each pf In pt.DataFields
pf.Caption = pf.SourceName & chr(160)
Next pf
End Sub
+++++++++

Sub SetAllDataItemstoSum()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
If pt Is Nothing Then
MsgBox "Cursor is not in PT"
Exit Sub
End If
For Each pf In pt.DataFields
pf.Function = xlSum
Next pf
End Sub
++++++++

Thank you,
I will post in the VBA forum as well.
Nataly