Solved

Pivot Table Bug

Posted on 2010-08-17
22
394 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

24 Experts available now in Live!

Get 1:1 Help Now