How to debug a Pivot Chart Macro?

Question

I am not able to run this, I want to count total rows in sheet and pass that to pivot chart to create.

1. Pivot chart create
2. select fileds
3. Double click grand total to create new spread sheet

Sub Macro2()

Dim ws As Worksheet
Dim lastRow As Long
Set ws = ActiveSheet
NewSheet = ActiveSheet.Name
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    ws & "!R1C1:R" & lastRow & "C15",    
Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:=NewSheet & "!R1C1", TableName:="PivotTable1",   
DefaultVersion _
    :=xlPivotTableVersion14

Sheets("NewSheet").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet4!$A$1:$C$18")
ActiveSheet.Shapes("Chart 1").IncrementLeft 192
ActiveSheet.Shapes("Chart 1").IncrementTop 15
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(  
_
 "PivotTable1").PivotFields("Customer"), "Count of Customer", xlCount
ActiveWindow.SmallScroll Down:=12
Range("B29").Select
Selection.ShowDetail = True
End Sub'

Answer 1

The code below checks the data in Sheet1 (modify to your sheet name) and creates a Pivot Table and Chart in Sheet Report.

On first time it creates the Pivot Table and chart, from the second time it just refreshes the Pivot Cache with the updated rows of data (in Sheet1) and updates the Chart.

Sub Macro2()

Dim sht1                            As Worksheet
Dim shtReport                       As Worksheet
Dim lastRow                         As Long
Dim PivotSrc_Range                  As Range

Dim PvtCache                        As PivotCache
Dim PvtTbl                          As PivotTable
Dim Chart1                          As Chart

' modify to your sheet name
Set sht1 = ThisWorkbook.Sheets("Sheet1")

' modify to your desired Pivot Table location
Set shtReport = ThisWorkbook.Sheets("Report")

' create the Source Range of the Pivot Cache
lastRow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row

' it's looking uo tp Column "O" (15) as recorded in your MACRO
Set PivotSrc_Range = sht1.Range(sht1.Cells(1, 1), sht1.Cells(lastRow, 15))

' set the Pivot Cache
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotSrc_Range, Version:=xlPivotTableVersion14)

On Error Resume Next
Set PvtTbl = shtReport.PivotTables("PivotTable1")

On Error GoTo 0
If PvtTbl Is Nothing Then
    ' create a new Pivot Table in "Report" sheet, start from Cell A2
    Set PvtTbl = shtReport.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=shtReport.Range("A2"), TableName:="PivotTable1")

    ' modify the name in brackets according to your Pivot Fields
    With PvtTbl.PivotFields("Customer")
        .Orientation = xlRowField
        .Position = 1
    End With        

    PvtTbl.AddDataField PvtTbl.PivotFields("Customer"), "Count of Customer", xlCount

Else
    ' just refresh the Pivot cache with the updated Range (data in Sheet1)
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If

' check if already has a chart in sheet (from previous Macro Runs)
If shtReport.ChartObjects.Count >= 1 Then
    Set Chart1 = shtReport.ChartObjects(1).Chart
Else ' first time >> create the chart
    shtReport.Shapes.AddChart.Select
    Set Chart1 = ActiveChart
End If

With Chart1
    .ChartType = xlColumnClustered
    .SetSourceData Source:=PvtTbl.TableRange1 ' refresh the chart with the updated Pivot Table
End With


End Sub