Solved

Pivot Table Bug

Posted on 2010-08-17
22
392 Views
Last Modified: 2013-11-05
Call All Gurus -

I am trying to create a pivot field called "Program Name". When I add: .PivotFields("Program Name").CurrentPage = "Data Centers"
 
At the top of the code it blows up at:

   act = act + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name", proj, "Version", "Act/Forecast", "Unit Type", "USD")

Below see all of the code:



I am counting on you!


Sub AllProgs()



ResetPivot



Dim dash As Worksheet, dash2 As Worksheet

Set dash = Worksheets("DASHBOARD")

Set dash2 = Worksheets("DASHBOARD 2")

mo = Sheet13.Range("B1")

Dim report As Worksheet

Set report = Worksheets("All Programs")

report.Range("A5:Q200").Clear



Dim pv As PivotTable

Set pv = pvtSheet.PivotTables(1)



With pv

    .PivotFields("Version").Orientation = xlRowField

    .PivotFields("Unit Type").CurrentPage = "USD"

    .PivotFields("Program Name").CurrentPage = "Data Centers"

End With



For Each chartobj In report.ChartObjects

    chartobj.Delete

Next

report.Range("A5:A8").Value = "TOTAL"

report.Range("A5:A8").Font.Bold = True

report.Cells(5, 3) = "Baselined"

report.Cells(6, 3) = "Actuals/Estimate"

report.Cells(7, 3) = "Variance"

report.Cells(8, 3) = "% Variance"



act = 0

base = 0

For m = 1 To 12

    act = act + pv.GetPivotData("Sum of " & MonthName(m, True), "Version", "Act/Forecast")

    base = base + pv.GetPivotData("Sum of " & MonthName(m, True), "Version", "Baselined")

    If m = mo Then

        report.Cells(5, 5) = base

        report.Cells(6, 5) = act

        report.Cells(7, 5) = base - act

        report.Cells(8, 5) = (base - act) / base

    End If

Next

report.Cells(5, 6) = base

report.Cells(6, 6) = act

report.Cells(7, 6) = base - act

report.Cells(8, 6) = (base - act) / base

destrow = 10

dashrow = 5



report.Range("E5:F7").NumberFormat = "$#,##0"

report.Range("E8:F8").NumberFormat = "0%"



report.Cells(5, 7) = report.Cells(5, 6) - report.Cells(5, 5)

report.Cells(6, 7) = report.Cells(6, 6) - report.Cells(6, 5)



With report.ChartObjects.Add(report.Columns("G").Left, report.Rows(4).Top, 600, 66).Chart

    .ChartWizard Source:=Application.Union(Range(Cells(5, 5), Cells(6, 5)), Range(Cells(5, 7), Cells(6, 7))), _

                         Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, HasLegend:=False

    With .Axes(xlCategory)

        .ReversePlotOrder = True

        .Delete

    End With

    .Axes(xlValue).Delete

    .ChartGroups(1).GapWidth = 20

    With .PlotArea

        .Top = 0

        .Left = 0

        .Height = 60

        .Width = 585

    End With

    .Parent.Border.LineStyle = xlLineStyleNone

    With .SeriesCollection(1)

        .HasDataLabels = True

        For n = 1 To 2

          With .Points(n)

          If report.Cells(4 + n, 6) <> 0 Then

            .DataLabel.Text = Round(report.Cells(4 + n, 5) / report.Cells(4 + n, 6), 2) * 100 & "%"

          Else

            .DataLabel.Delete

          End If

          End With

        Next

    End With

    With .SeriesCollection(2)

        .HasDataLabels = True

        .Fill.ForeColor.SchemeColor = 2

        .Points(1).DataLabel.Text = "2010 Baselined: " & Format(Round(report.Cells(5, 6), 0), "$#,##0")

        .Points(2).DataLabel.Text = "2010 Estimate: " & Format(Round(report.Cells(6, 6), 0), "$#,##0")

        .DataLabels.Position = xlLabelPositionInsideEnd

    End With

End With







pv.PivotFields("Project Name").Orientation = xlRowField







pv.PivotFields("Unit Type").Orientation = xlRowField

With pv.PivotFields("Project Name")

    .Orientation = xlRowField

    .Position = 1

End With







For Each proj In pv.PivotFields("Project Name").PivotItems

  If proj <> "(blank)" And proj.RecordCount Then

    report.Cells(destrow, 1).Value = proj.Name

    dash.Cells(dashrow, 3) = proj.Name

    dash2.Cells(dashrow, 3) = proj.Name

    report.Cells(destrow, 5).Value = "YTD Total"

    report.Cells(destrow, 6).Value = "2010 Total"

    report.Rows(destrow).Font.Bold = True

destrow = destrow + 1

    report.Cells(destrow, 1).Value = Worksheets("Project reference").PivotTables(1).PivotFields("Project Name"). _

                                    PivotItems(proj.Name).LabelRange.Cells(1).Offset(0, 1)

    report.Cells(destrow, 3).Value = "Baselined"

    act = 0

    base = 0

    actF = 0

    baseF = 0

    For m = 1 To 12

        

        

        

       act = act + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name", proj, "Version", "Act/Forecast", "Unit Type", "USD")

        actF = actF + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name", proj, "Version", "Act/Forecast", "Unit Type", "FTE")

        On Error Resume Next

        If prog <> "Not in 2010 EQIT BoW" And prog.RecordCount Then base = base + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name", proj, "Version", "Baselined", "Unit Type", "USD"): baseF = baseF + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name", prog, "Version", "Baselined", "Unit Type", "FTE")

        On Error GoTo 0

        If m = mo Then

            dash2.Cells(dashrow, 5) = baseF / m

            dash2.Cells(dashrow, 4) = actF / m

            ytdBase = base

            ytdAct = act

        End If

        

          

    Next

    report.Cells(destrow, 5).Value = ytdBase

    report.Cells(destrow, 6).Value = base

    

    report.Cells(destrow, 5).NumberFormat = "$#,##0"

    report.Cells(destrow, 6).NumberFormat = "$#,##0"

    dash.Cells(dashrow, 5) = report.Cells(destrow, 5)

    dash.Cells(dashrow, 8) = report.Cells(destrow, 6)

    dash2.Cells(dashrow, 8) = baseF / 12

    dash2.Cells(dashrow, 7) = actF / 12

    

    report.Cells(destrow, 7) = report.Cells(destrow, 6) - report.Cells(destrow, 5)

    

destrow = destrow + 1

    report.Cells(destrow, 3).Value = "Actuals/Forcast"

    report.Cells(destrow, 5).Value = ytdAct

    report.Cells(destrow, 5).NumberFormat = "$#,##0"

    report.Cells(destrow, 6).Value = act

    report.Cells(destrow, 6).NumberFormat = "$#,##0"

    dash.Cells(dashrow, 4) = report.Cells(destrow, 5)

    dash.Cells(dashrow, 7) = report.Cells(destrow, 6)

    report.Cells(destrow, 7) = report.Cells(destrow, 6) - report.Cells(destrow, 5)

      

destrow = destrow + 1



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    report.Cells(destrow, 1).Value = "Project"

    report.Cells(destrow + 1, 1).Value = "view"

    report.Range(report.Cells(destrow, 1), report.Cells(destrow + 1, 1)).HorizontalAlignment = xlCenter

    report.Range(report.Cells(destrow, 1), report.Cells(destrow + 1, 1)).Interior.ColorIndex = 15

    report.Range(report.Cells(destrow, 1), report.Cells(destrow + 1, 1)).BorderAround Weight:=xlThick

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    

    report.Cells(destrow, 3).Value = "Variance"

    For m = 0 To 1

        report.Cells(destrow, 5 + m).Value = report.Cells(destrow - 2, 5 + m).Value - report.Cells(destrow - 1, 5 + m).Value

        report.Cells(destrow, 5 + m).NumberFormat = "$#,##0"

    Next

    dash.Cells(dashrow, 9) = report.Cells(destrow, 6) * -1

    dash.Cells(dashrow, 6) = report.Cells(destrow, 5) * -1

destrow = destrow + 1

    

    report.Cells(destrow, 3) = "% Variance"

    report.Rows(destrow).NumberFormat = "0%"

    For m = 0 To 1

        If report.Cells(destrow - 3, 5 + m) = 0 Then

            report.Cells(destrow, 5 + m) = 0

        Else

            report.Cells(destrow, 5 + m) = report.Cells(destrow - 1, 5 + m) / report.Cells(destrow - 3, 5 + m)

        End If

    If report.Cells(destrow, 5 + m) > 0 Then

        report.Cells(destrow - 1, 8 + m) = 1 - report.Cells(destrow, 5 + m)

        report.Cells(destrow, 8 + m) = report.Cells(destrow, 5 + m)

    Else

        report.Cells(destrow - 1, 8 + m) = 1

        report.Cells(destrow, 8 + m) = report.Cells(destrow, 5 + m) * -1

    End If

    report.Cells(destrow - 1, 8 + m).NumberFormat = "0%"

    Next

    

    With report.ChartObjects.Add(report.Columns("G").Left, report.Rows(destrow - 4).Top, 600, 66).Chart

        .ChartWizard Source:=Application.Union(Range(Cells(destrow - 3, 5), Cells(destrow - 2, 5)), Range(Cells(destrow - 3, 7), Cells(destrow - 2, 7))), _

                         Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, HasLegend:=False

        With .Axes(xlCategory)

            .ReversePlotOrder = True

            .Delete

        End With

        .Axes(xlValue).Delete

        .ChartGroups(1).GapWidth = 20

        With .PlotArea

            .Top = 0

            .Left = 0

            .Height = 60

            .Width = 585

        End With

        .Parent.Border.LineStyle = xlLineStyleNone

        With .SeriesCollection(1)

            .HasDataLabels = True

            For n = 1 To 2

              With .Points(n)

                If report.Cells(destrow - 4 + n, 6) <> 0 Then

                    .DataLabel.Text = Round(report.Cells(destrow - 4 + n, 5) / report.Cells(destrow - 4 + n, 6), 2) * 100 & "%"

                Else

                    .DataLabel.Delete

                End If

              End With

            Next

        End With

        With .SeriesCollection(2)

            .HasDataLabels = True

            .Fill.ForeColor.SchemeColor = 2

            .Points(1).DataLabel.Text = "2010 Baselined: " & Format(Round(report.Cells(destrow - 3, 6), 0), "$#,##0")

            .Points(2).DataLabel.Text = "2010 Estimate: " & Format(Round(report.Cells(destrow - 2, 6), 0), "$#,##0")

            .DataLabels.Position = xlLabelPositionInsideEnd

        End With

    End With

    

    

    destrow = destrow + 1

    

    report.Cells(destrow, 1) = "                                                                                                                                                                                                                                                                                                                        "

    report.Cells(destrow, 1).Font.Strikethrough = True

    destrow = destrow + 1

    dashrow = dashrow + 1

  End If

Next





End Sub

Open in new window

0
Comment
Question by:Kirkeous
  • 13
  • 5
  • 3
22 Comments
 

Author Comment

by:Kirkeous
ID: 33459436
The end goal is for the user to select a choice from the combo box and the code set the pivot for the "Program Name" and the data post to Dashboard. I can see the "Program Name" change on the pivot table and it displays the correct data there but it will not post!
0
 

Author Comment

by:Kirkeous
ID: 33460123
This is where it breaks in the code:

  act = act + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name", proj, "Version", "Act/Forecast", "Unit Type", "USD")
0
 

Author Comment

by:Kirkeous
ID: 33460524
Would it help if I zip up the file? It is under 2mb . . .
0
 
LVL 45

Expert Comment

by:aikimark
ID: 33460543
Sure.  Or you can just attach the workbook.
0
 

Author Comment

by:Kirkeous
ID: 33460552
I can't! I just saw this would be public information!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 33460562
Can you 'fuzz' the data?
0
 

Author Comment

by:Kirkeous
ID: 33460627
I'd rather email it directly to you? Or no? I am worried about intellectual property issues . . .
0
 
LVL 45

Expert Comment

by:aikimark
ID: 33460634
EE procedures do not allow you to provide a direct transfer of files to the experts.  Everything pertaining to the question's solution are publicly visible.
0
 

Author Comment

by:Kirkeous
ID: 33460671
Ok then. I hope the code is enough. Here is the walk through. I added this line of code to set the pivot table:

(See 19)   .PivotFields("Program Name").CurrentPage = "Data Centers"
 
and it breaks here:

(See 128)   act = act + pv.GetPivotData("Sum of " & MonthName(m, True), "Project Name . . .

When it does work I will change (See 19) to:

 .PivotFields("Program Name").CurrentPage = ComboBox1.Value

I hope this helps! When I check the pivot. It is sorted correctly but it craps out when it is writing to the report! I spent 4 hours on this today and I hate my life.


0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33463880
Are you sure you can't censor the data enough to post? It would make life a lot easier.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Kirkeous
ID: 33465628
Here it is . . .
PMO-REPORT-2010Aug-v13h.zip
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33465706
Runs OK for me following your instructions.
0
 

Author Comment

by:Kirkeous
ID: 33465738
Hmm, hold on one sec. Do you see 2 lines commented out? If you do then uncomment them and run it again.

My mistake. It should break when you do this. And therein lies the problem....
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33465983
OK found those and you also didn't have the Currentpage bit in.
The error is because the data is not displayed on the table (same as if you use Getpivotdata as a worksheet function and the data isn't there). Add this function to your module:
Function HasData(ByVal pi As PivotItem) As Boolean
    HasData = False
    On Error Resume Next
    HasData = Not (pi.DataRange Is Nothing)
End Function


and then change this line:
If proj <> "(blank)" And proj.RecordCount  Then
to this:
If proj <> "(blank)" And proj.RecordCount And HasData(proj) Then
0
 

Author Comment

by:Kirkeous
ID: 33466351
Ok! Good job. That worked great. next problem is it is not resetting the Dashboard or Dashboard2 or All Programs....
0
 

Author Comment

by:Kirkeous
ID: 33466392
Basically, need it to erase and regenerate the Projects....
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33466421
You just need to clear the ranges and then reformat them (if required). That's a different question though. ;)
0
 

Author Comment

by:Kirkeous
ID: 33466700
You are so right. If you are ever in NYC let me know. I owe you a steak dinner!
0
 

Author Closing Comment

by:Kirkeous
ID: 33466713
He was very efficient and responsive. God bless him.
0
 

Author Comment

by:Kirkeous
ID: 33468246
Rorya -  The only thing is the totals are not showing up!!! Can you hear me?!?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33468607
What totals?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now