新工作表上的 VBA 数据透视表

VBA Pivot Table On New Sheet

提问by Nicholas Brandon

I have recorded a macro to create a pivot table and subsequent chart in VBA. It works like a charm, just not at all how I need it to. The problem is that I want to be able to run the code and have it create a table on a sheet that doesn't already exist. Basically I will run this from a button on a menu, and it should create the the table and chart on a new page. Without further adieu:

我已经录制了一个宏来在 VBA 中创建数据透视表和后续图表。它就像一种魅力，只是完全不是我需要的。问题是我希望能够运行代码并让它在不存在的工作表上创建一个表格。基本上我将从菜单上的按钮运行它，它应该在新页面上创建表格和图表。不用多说：

Sub Macro13()

' Macro13 Macro



    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Main Budget Sheet!R2C1:R88C10", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Sheet21!R1C1", TableName:= _
        "PivotTable12", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet21").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("Category")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable12").AddDataField ActiveSheet.PivotTables( _
        "PivotTable12").PivotFields("Labor"), "Sum of Labor", xlSum
    ActiveSheet.PivotTables("PivotTable12").AddDataField ActiveSheet.PivotTables( _
        "PivotTable12").PivotFields("Material"), "Sum of Material", xlSum
    ActiveSheet.Shapes.AddChart.Select
End Sub

If I was putting this into plain English, I would assume that it means... Select Data in Main Budget Sheet then create a pivot table on sheet 21 named pivot table 12... Then Select sheet 21 and select the fields for the pivot table...Finally, add chart from that data...

如果我把它变成简单的英语，我会假设这意味着......在主预算表中选择数据，然后在工作表 21 上创建一个名为数据透视表 12 的数据透视表......然后选择工作表 21 并选择数据透视表的字段表...最后，从该数据添加图表...

I think what I need to do is just figure out general names for each of these things (besides source data).

我认为我需要做的只是找出这些东西的通用名称（除了源数据）。

Also, currently it will only run once and only if that sheet already exists, presumably because it can't make a pivot table named the same thing, so if it could repeatedly just create the same table on different sheets I could die happy.

此外，目前它只会运行一次，并且仅当该工作表已经存在时，大概是因为它无法创建一个名称相同的数据透视表，所以如果它可以重复地在不同的工作表上创建相同的表，我可能会很高兴。

As you can see, I've tried this enough that I have created 21 sheets :/ so the effort is there, but as Einstein definition of insanity states "Insanity is trying the same thing and expecting a different result." Currently, I'm running out of new things to try.

正如你所看到的，我已经尝试了很多，我已经创建了 21 张：/所以努力就在那里，但正如爱因斯坦对疯狂的定义所说的那样“疯狂正在尝试同样的事情并期待不同的结果。” 目前，我已经没有新的东西可以尝试了。

回答by user2140261

Add a new sheet then use that sheet to add the new pivot too as follow:

添加一个新工作表，然后使用该工作表添加新的枢轴，如下所示：

Sub AddPivotOnNewSheet()
Dim wsNew As Worksheet

Set wsNew = Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Main Budget Sheet!R2C1:R88C10", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:=wsNew.Name & "!R1C1", TableName:= _
        "PivotTableName", DefaultVersion:=xlPivotTableVersion14

    With ActiveSheet.PivotTables("PivotTableName").PivotFields("Category")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTableName").AddDataField ActiveSheet.PivotTables( _
        "PivotTableName").PivotFields("Labor"), "Sum of Labor", xlSum
    ActiveSheet.PivotTables("PivotTableName").AddDataField ActiveSheet.PivotTables( _
        "PivotTableName").PivotFields("Material"), "Sum of Material", xlSum

    wsNew.Shapes.AddChart.Select
End Sub

回答by Riya Sen

Sub pivotnewsheet()

Dim ws As Worksheet 
Dim pc As PivotCache 
Dim pt As PivotTable

'Creating Pivot cache 
On Error Resume Next 
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, Cells(5, 1).CurrentRegion) 'select first cells of your data range, in my case its start with Row5 column1 so i put cells(5,1)

'Adding new worksheet

Set ws = Worksheets.Add 
ws.Name = "Summary"

'Creating Pivot table 
Set pt = ActiveSheet.PivotTables.Add(PivotCache:=pc, Tabledestination:=Range("A3"))
' it will create pivot table on sheet named summary at range A3

pt.Name = "PivotTable"

'Setting Fields 
With pt 
with .PivotFields("DEPT_NAME") 
.Orientation = xlRowField 
.Position = 1 
End With

'set column field 
With .PivotFields("QTY") 
.Orientation = xlDataField 
.Function = xlSum 
.Position = 1 
End With 
With .PivotFields("TOTAL_RETAIL_VALUE") 
.Orientation = xlDataField 
.Function = xlAverage 
.Position = 2 
.NumberFormat = "0.00" 'format for two decimals 
End With 
With .PivotFields("TOTAL_LANDED_COST") 
.Orientation = xlDataField 
.Function = xlAverage 
.NumberFormat = "0.00" 
.Position = 3 
End With 
'select classic view pivot 
With ActiveSheet.PivotTables("PivotTable") 
.InGridDropZones = True 
.RowAxisLayout xlTabularRow 
End With

End With

End Sub