新表上的VBA数据透视表 [英] VBA Pivot Table On New Sheet

问题描述

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

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:

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

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

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...

我想我需要做的只是找出每个这些的一般名称事情（源数据除外）。

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.

正如你所看到的，我已经尝试了创造了21页：/ /所以努力是在那里，但爱因斯坦对精神错乱的定义说疯狂正在尝试同样的事情，并期待不同的结果。目前，我正在用新的东西来尝试。

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.

推荐答案

添加新的表单，然后使用该工作表添加新的枢轴也如下：

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

这篇关于新表上的VBA数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！