?
Solved

Pivot Table Bug

Posted on 2010-08-17
22
Medium Priority
?
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

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 46

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 46

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
 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

752 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