• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Pivot Table Bug

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
Kirkeous
Asked:
Kirkeous
  • 13
  • 5
  • 3
1 Solution
 
KirkeousAuthor Commented:
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
 
KirkeousAuthor Commented:
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
 
KirkeousAuthor Commented:
Would it help if I zip up the file? It is under 2mb . . .
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
aikimarkCommented:
Sure.  Or you can just attach the workbook.
0
 
KirkeousAuthor Commented:
I can't! I just saw this would be public information!
0
 
aikimarkCommented:
Can you 'fuzz' the data?
0
 
KirkeousAuthor Commented:
I'd rather email it directly to you? Or no? I am worried about intellectual property issues . . .
0
 
aikimarkCommented:
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
 
KirkeousAuthor Commented:
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
 
Rory ArchibaldCommented:
Are you sure you can't censor the data enough to post? It would make life a lot easier.
0
 
KirkeousAuthor Commented:
Here it is . . .
PMO-REPORT-2010Aug-v13h.zip
0
 
Rory ArchibaldCommented:
Runs OK for me following your instructions.
0
 
KirkeousAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
KirkeousAuthor Commented:
Ok! Good job. That worked great. next problem is it is not resetting the Dashboard or Dashboard2 or All Programs....
0
 
KirkeousAuthor Commented:
Basically, need it to erase and regenerate the Projects....
0
 
Rory ArchibaldCommented:
You just need to clear the ranges and then reformat them (if required). That's a different question though. ;)
0
 
KirkeousAuthor Commented:
You are so right. If you are ever in NYC let me know. I owe you a steak dinner!
0
 
KirkeousAuthor Commented:
He was very efficient and responsive. God bless him.
0
 
KirkeousAuthor Commented:
Rorya -  The only thing is the totals are not showing up!!! Can you hear me?!?
0
 
Rory ArchibaldCommented:
What totals?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now