使用vba添加数据透视表而不使用指定的数据透视表名称 [英] Adding Pivot charts using vba without using specified pivot chart names

问题描述

我有以下代码，我的代码基于从一组数据制作数据透视图，我遇到的唯一问题是激活字段和他们需要去的地方。因为我需要这是一个通用代码，所以我可以用它来进行多项工作
sheet我不知道应该用什么代替Pivottable（＃）。我还需要它在4个不同的工作表上运行。

I have the following code I was given to base my code off of to make pivot charts from a set of data, the only problem I run in to is activating fields and where they need to go. Since I need this to be a general code so i can use it for multiple work sheets I'm not sure what to put in place of Pivottable(#). i also need it to run on 4 different worksheets.

Sub Table()
Dim Prange(3, 1) As Range
Dim PT As PivotTable
Dim A, C As Long
'Set target ranges and destination ranges
Set Prange(0, 0) = Range("B2", Range("B2").End(xlDown).End(xlToRight))
Set Prange(0, 1) = Cells(2, 13)
Set Prange(1, 0) = Range("A2", Range("A2").End(xlDown))
Set Prange(1, 1) = Cells(32, 13)
Set Prange(2, 0) = Range("H2", Range("H2").End(xlDown).End(xlToRight))
Set Prange(2, 1) = Cells(3, 21)
Set Prange(3, 0) = Range("G2", Range("G2").End(xlToRight))
Set Prange(3, 1) = Cells(31, 21)
C = 1
'here is were you'd add in the worksheet command to cycle through
For Each PT In ActiveWorkbook.ActiveSheet.PivotTables
PT.TableRange2.Clear
Next PT
For A = 1 To 4
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Prange(A - 1, 0), Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=Prange(A - 1, 1), TableName:="PivotTable" & C, _
        DefaultVersion:=xlPivotTableVersion10
'here you can add in additional code depending on the pivot table being created, for example, if A=1 then blahblahblah
If A = 1 Then
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("'Combined 43'!$M$2:$S$15")
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Brand")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Code"), "Sum of Code", xlSum
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Width")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of Code")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Brand"), "Count of Brand", xlCount
    ActiveWindow.Zoom = 90
    ActiveWindow.Zoom = 80
    ActiveSheet.Shapes("Chart 3").IncrementLeft 312.187480315
    ActiveSheet.Shapes("Chart 3").IncrementTop -40.312519685
ElseIf A = 2 Then
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Rolls")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Rolls"), "Sum of Rolls", xlSum
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Sum of Rolls")
        .Caption = "Count of Rolls"
        .Function = xlCount
    End With
ElseIf A = 3 Then
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Range("'Combined 43'!$U$3:$AA$16")
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("Brand"), "Count of Brand", xlCount
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Width")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveWindow.Zoom = 70
    ActiveWindow.Zoom = 60
    ActiveSheet.Shapes("Chart 4").IncrementLeft 613.75
    ActiveSheet.Shapes("Chart 4").IncrementTop -106.25
    Range("G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Combined 43!R2C7:R237C7", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Combined 43!R31C21", TableName:="PivotTable8", _
        DefaultVersion:=xlPivotTableVersion10
ElseIf A = 4 Then
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Roll")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("Roll"), "Sum of Roll", xlSum
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Sum of Roll")
        .Caption = "Count of Roll"
        .Function = xlCount
    End With
End If
C = C + 1
Next A
End Sub

推荐答案

嗨Tjgrassi，

Hi Tjgrassi,

我不确定是否完全理解代码。由于您创建名为
TableName：=" PivotTable" &安培; C ，c来自1.但代码中的名称是PivotTable5 到数据透视表8。

I am not sure understand the code exactly. Since you create pivot table with name TableName:="PivotTable" & C, and c is from 1. But the name in your code is PivotTable5  to PivotTable 8.

但由于数据透视表是针对整个工作簿的，所以如果你想在多个工作表中创建数据透视表，我建议你命名它与sepcifct工作表名称。例如，Sheet1_PivotTable1。然后，您可以在代码中引用它们，例如
  ActiveSheet 。 数据透视表 （ " Sheet1_PivotTable1 " ）。

But since the pivot table is reate to the whole workbook, if you want to create the pivot table in mutiple worksheet, I suggest you name it with sepcifct worksheet name. For example, Sheet1_PivotTable1. Then you can refer them in the code like  ActiveSheet.PivotTables("Sheet1_PivotTable1").

希望它有用。

祝你好运

Fei

这篇关于使用vba添加数据透视表而不使用指定的数据透视表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！