Solved

Pivot Table Bug

Posted on 2010-08-17
22
396 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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 descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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