BvA - Spending Rate and Balance

[Indra]

Hi all,

I have BvA report (budget vs actual) with pivot table.
But I still need manually create column for spending rate and budget balance.
It would be nice if there is a way to create the spending rate and budget balance automatically, probably through vba or else.
So the two column can adjust if the pivot table expanded by month or by additional column next to budget header column.
I tried with calculated field or item but it can't. file attached.

Thank you and best regards,
Indra

[HansV]

Here is a macro:

Code: Select all

Sub UpdatePivot()
    Dim wsh As Worksheet
    Dim pvt As PivotTable
    Dim rngP As Range
    Dim rngE As Range
    Dim col1 As Long
    Dim col2 As Long
    Set wsh = Worksheets("BvA - Summary")
    Set pvt = wsh.PivotTables(1)
    Set rngP = pvt.TableRange1
    Set rngE = rngP.Offset(ColumnOffset:=rngP.Columns.Count).Resize(ColumnSize:=2)
    rngE.Clear
    Application.DisplayAlerts = False
    pvt.PivotCache.Refresh
    Application.DisplayAlerts = True
    Set rngP = pvt.TableRange1
    Set rngE = rngP.Offset(ColumnOffset:=rngP.Columns.Count).Resize(ColumnSize:=2)
    With rngE.Borders
        .LineStyle = xlContinuous
        .Color = 10066329
    End With
    rngE.Rows(2).Interior.Color = vbYellow
    With rngE.Rows(3)
        .Value = Array("Spending Rate", "Budget Balance")
        .Font.Bold = True
        .Font.Color = vbRed
        .Borders(xlEdgeBottom).LineStyle = xlNone
    End With
    rngE.Rows(4).Borders(xlEdgeBottom).LineStyle = xlNone
    col1 = rngP.Column + 4
    col2 = rngE.Column - 1
    With rngE.Cells(6, 1).Resize(RowSize:=rngE.Rows.Count - 5)
        .FormulaR1C1 = "=IF(RC" & col1 & "=0,0,RC" & col2 & "/RC" & col1 & ")"
        .NumberFormat = "0%_);(0%);""-""_)"
    End With
    With rngE.Cells(6, 2).Resize(RowSize:=rngE.Rows.Count - 5)
        .FormulaR1C1 = "=RC" & col1 & "-RC" & col2
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_)"
    End With
    Application.ScreenUpdating = True
End Sub

[Indra]

Million thanks, Hans.

regards,
Indra

[Indra]

I'm a happiest man.
Thank you, Hans!

[HansV]

Good to hear that!

[Indra]

Hello everyone.

I'd like to continue this threat, by asking, if possible to have another code to create the forecast table as attached.
Once I send out the BvA, I need to have a meeting with the program manager to have a forecast table.
It's a repeating task, therefore would be helpful if the additional forecast table can be created through another VBA code.

Thank you in advance for your attention and help.

best,
Indra

[HansV]

I'm working on it, but there's a lot to do!

[Indra]

Dear Hans,

Appreciate the respond, fully understand if this is too much.

Warmly.
Indra

[HansV]

It's not too much, but it'll take some time. I hope to have a response for you tomorrow (your time).

[HansV]

See the attached workbook. Module2 contains a macro that will generate the Forecast sheet.
Warning: any existing data on the Forecast sheet will be overwritten.

BvA - spending rate & budget balance.xlsm

[Indra]

Hi Hans,

Many thanks, I can save lot of my times.
Have tried with different number of rows and subtotal. It works.

Best,
Indra