Solved

Excel Pivot Table not display the result using VBA

Posted on 2016-11-23
10
59 Views
Last Modified: 2016-11-24
While run the macro in Step-In mode pressing F8 key the result display but when run the macro directly it is not displaying the result.

If select either row field or Column field of the Pivot Table and click the OK Button, the result appear.

What is the problem in VBA Code.

VBA Code:

Sub PT_Create_Env()

' Code to Generate a Pivot Table

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Sheet1")

' Delete any prior pivot tables

    For Each PT In WSD.PivotTables

        PT.TableRange2.Clear

    Next PT

' Define Input area and set up a Pivot Cache

    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                    SourceData:=PRange)

' Create a Pivot Table from Pivot Cache
    Set PT = PTCache.CreatePivotTable(tabledestination:=WSD. _
                Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
    PT.ManualUpdate = True

' Setup the row and column fields
    PT.AddFields RowFields:="Sample Number", _
        ColumnFields:="Parameter:"

' Set up the data fields
    With PT.PivotFields("Result")
        .Orientation = xlDataField
        .Function = xlSum
        '.Position 1
        .NumberFormat = "#.##0"
        .Name = "Result "
    End With

' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

' Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
   
    With PT
        .ColumnGrand = False
        .RowGrand = False
'        .repeatalllables xlRepeatLabels    ' New in Excel 2010
    End With
   
    PT.PivotFields("Sample Number").Subtotals(1) = True
    PT.PivotFields("Sample Number").Subtotals(1) = False
    WSD.Activate
'    Range("P2").Select
End Sub
DR-2800-091116.xlsm
0
Comment
Question by:Arun Majumder
  • 5
  • 3
  • 2
10 Comments
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41900125
Try commenting out the line PT.ManualUpdate = True
1
 

Author Comment

by:Arun Majumder
ID: 41900146
Thank you for the response Mr Neeraj. I have already tried the option previously with no solution.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41900163
Hi,

pls try
Sub PT_Create_Env()

' Code to Generate a Pivot Table

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Sheet1")

' Delete any prior pivot tables

    For Each PT In WSD.PivotTables

        PT.TableRange2.Clear

    Next PT

' Define Input area and set up a Pivot Cache

    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                    SourceData:=PRange)

' Create a Pivot Table from Pivot Cache
    Set PT = PTCache.CreatePivotTable(tabledestination:=WSD. _
                Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
    PT.ManualUpdate = True

' Setup the row and column fields
    PT.AddFields RowFields:="Sample Number", _
        ColumnFields:="Parameter:"

' Set up the data fields
    With PT.PivotFields("Result")
        .Orientation = xlDataField
        .Function = xlSum
        '.Position 1
        .NumberFormat = "#.##0"
        .Name = "Result "
    End With

' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

' Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
    
    With PT
        .ColumnGrand = False
        .RowGrand = False
'        .repeatalllables xlRepeatLabels    ' New in Excel 2010
    End With
    
    PT.PivotFields("Sample Number").Subtotals(1) = True
    PT.PivotFields("Sample Number").Subtotals(1) = False
    PT.PivotCache.Refresh
    WSD.Activate
'    Range("P2").Select


    
End Sub

Open in new window

Regards
0
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.

 

Author Comment

by:Arun Majumder
ID: 41900436
Thank you @Rgonzo1971 for the reply but same is also tried earlier with no solution.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41900459
then try
Sub PT_Create_Env()

' Code to Generate a Pivot Table

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Sheet1")

' Delete any prior pivot tables

    For Each PT In WSD.PivotTables

        PT.TableRange2.Clear

    Next PT

' Define Input area and set up a Pivot Cache

    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                    SourceData:=PRange)

' Create a Pivot Table from Pivot Cache
    Set PT = PTCache.CreatePivotTable(tabledestination:=WSD. _
                Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
    PT.ManualUpdate = True

' Setup the row and column fields
    PT.AddFields RowFields:="Sample Number", _
        ColumnFields:="Parameter:"

' Set up the data fields
    With PT.PivotFields("Result")
        .Orientation = xlDataField
        .Function = xlSum
        '.Position 1
        .NumberFormat = "#.##0"
        .Name = "Result "
    End With

' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

' Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
    
    With PT
        .ColumnGrand = False
        .RowGrand = False
'        .repeatalllables xlRepeatLabels    ' New in Excel 2010
    End With
    
    PT.PivotFields("Sample Number").Subtotals(1) = True
    PT.PivotFields("Sample Number").Subtotals(1) = False
    For Each pc In ActiveWorkbook.PivotCaches
        pc.Refresh
    Next pc
    WSD.Activate
'    Range("P2").Select

    
End Sub

Open in new window

Regards
0
 

Author Comment

by:Arun Majumder
ID: 41900787
Thank you @Neeraj for best solution. By mistake I have commented the following line which is appeared 1st.
' Turn off updating while building the table
 '   PT.ManualUpdate = True     ' It has no impact on dispaly

But while I have commented the second line which is under Calculate the Excel Pivot Table i.e.
' Calc the pivot table
    PT.ManualUpdate = False
'   PT.ManualUpdate = True   ' Commenting out this line solve the problem

Thank you for wonderful solution to @Neeraj and thanks to @Rgonzo1971 for your kind response.

Final VBA Code with Solution:

Sub PT_Create_Env()

' Code to Generate a Pivot Table

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Sheet1")

' Delete any prior pivot tables

    For Each PT In WSD.PivotTables

        PT.TableRange2.Clear

    Next PT

' Define Input area and set up a Pivot Cache

    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                    SourceData:=PRange)

' Create a Pivot Table from Pivot Cache
    Set PT = PTCache.CreatePivotTable(tabledestination:=WSD. _
                Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
    PT.ManualUpdate = True

' Setup the row and column fields
    PT.AddFields RowFields:="Sample Number", _
        ColumnFields:="Parameter:"

' Set up the data fields
    With PT.PivotFields("Result")
        .Orientation = xlDataField
        .Function = xlSum
        '.Position 1
        .NumberFormat = "#.##0"
        .Name = "Result "
    End With

' Calc the pivot table
    PT.ManualUpdate = False
'    PT.ManualUpdate = True

' Format the pivot table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "PivotStyleMedium10"
   
    With PT
        .ColumnGrand = False
        .RowGrand = False
'        .repeatalllables xlRepeatLabels    ' New in Excel 2010
    End With
   
    PT.PivotFields("Sample Number").Subtotals(1) = True
    PT.PivotFields("Sample Number").Subtotals(1) = False
   
    WSD.Activate
'    Range("P2").Select

   
End Sub
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41900827
You're welcome Arun!
Since you didn't post your code within the code tags, I missed the first line of code which you commented out. If the code was posted within the code tags, it would have been  easy to point out the correct line of code by the line no. :)
But glad you commented out the correct line in the end and it worked for you.
1
 

Author Comment

by:Arun Majumder
ID: 41901115
This is my 1st time experience to get solution through my problem posting here. Last one week I searched for the solution over internet but the way I got my solution quickly is amazing. Thank you once again.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41901117
Thanks for the feedback Arun! We are happy to help.
Please take a moment to close the question by accepting the answer provided.
0
 

Author Closing Comment

by:Arun Majumder
ID: 41901120
Thank you for the effective and quick solution. Regards
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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