Skip to Content
0
Former Member
Apr 26, 2014 at 08:35 AM

Macros from 7.0 to EPM 10

20 Views

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