• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

Excel Pivot Table not display the result using VBA

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
Arun Majumder
Asked:
Arun Majumder
  • 5
  • 3
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try commenting out the line PT.ManualUpdate = True
1
 
Arun MajumderChief Manager MiningAuthor Commented:
Thank you for the response Mr Neeraj. I have already tried the option previously with no solution.
0
 
Rgonzo1971Commented:
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
Arun MajumderChief Manager MiningAuthor Commented:
Thank you @Rgonzo1971 for the reply but same is also tried earlier with no solution.
0
 
Rgonzo1971Commented:
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
 
Arun MajumderChief Manager MiningAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Arun MajumderChief Manager MiningAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Thanks for the feedback Arun! We are happy to help.
Please take a moment to close the question by accepting the answer provided.
0
 
Arun MajumderChief Manager MiningAuthor Commented:
Thank you for the effective and quick solution. Regards
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now