Macros from 7.0 to EPM 10

[Former Member]

Hi,

Can anybody guide me on the changes required in the VBA when migrating from BPC 7.0 to EPM 10.

What changes I need to incorporate in the following???

VBA in 7.0

Sub AddLinesAtTargetList()
Dim r As Long

'---update pivot table
Sheet_Pivot.PivotTables("PivotTable1").PivotCache.Refresh

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


r = Sheet_Pivot.PivotTables("PivotTable1").PivotRowAxis.PivotLines.Count

Range("TargetList").ClearContents

If Range("TargetList").Cells.Count = 2 Then
'MsgBox Range("TargetList").Cells.Count
Else
For x = Range("TargetList").Cells.Count - 1 To 2 Step -1
  Range("TargetList").Cells(x, 1).EntireRow.Delete
Next
'MsgBox Range("TargetList").Cells.Count
End If
For x = Sheet_Pivot.PivotTables("PivotTable1").PivotRowAxis.PivotLines.Count To 2 Step -1
  Range("TargetList").Cells(2, 1).EntireRow.Insert
  Range("TargetList").Cells(2, 1) = Sheet_Pivot.Range(Sheet_Pivot.PivotTables("PivotTable1").RowRange.Address).Cells(x, 1)
Range("OriginRange").Copy
  Range(Range("TargetList").Cells(2, 1).Address).Offset(0, 1).PasteSpecial xlPasteFormulas
Next

'MsgBox Range("TargetList").Address
If Range("TargetList").Cells.Count > 3 Then
  Range("TargetList").Cells(Range("TargetList").Cells.Count, 1).EntireRow.Delete
  Range("TargetList").Cells(1, 1).EntireRow.Delete
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Macro1()
'
' Macro1 Macro
'

'
'ActiveSheet.Previous.Select
Range("B5").Select
  ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotSelect _
"'Project classifiction'[All]", xlLabelOnly, True
'    With Selection.Interior
'        .Pattern = xlSolid
'        .PatternColorIndex = xlAutomatic
'        .Color = 65535
'        .TintAndShade = 0
'        .PatternTintAndShade = 0
'    End With
'    Range("I8").Select
End Sub

[former_member186338 · Accepted solution]

Hi Pankaj,

The provided macro has no BPC related instructions, just some standard Excel Pivot Table manipulation.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I did suspect that but got confused because after pasting this code in the epm add in,it gave an error.

Thanks for the reply.