Solved

Excel Pivot Table not display the result using VBA

Posted on 2016-11-23
10
34 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 28

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 48

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
 

Author Comment

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

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 28

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 28

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

12 Experts available now in Live!

Get 1:1 Help Now