Solved

Excel Pivot Table not display the result using VBA

Posted on 2016-11-23
10
43 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 49

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 49

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

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

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.…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

18 Experts available now in Live!

Get 1:1 Help Now