如何调试数据透视图宏？ [英] How to debug a Pivot Chart Macro?

问题描述

我不能运行这个，我想计算表中的总行数，并将其转换为透视图创建。

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

1. 数据透视表创建


2. 选择文件


3. 双击总计创建新的电子表格

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'

推荐答案

下面的代码检查数据在 Sheet1 （修改为您的工作表名称）并创建sa表格中的数据透视表和图表 报告 。

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

首次创建数据透视表和图表时，从第二次它只是使用更新的数据行刷新Pivot Cache（在 Sheet1 中）并更新图表。

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

这篇关于如何调试数据透视图宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！