VBA Pivot Table On New Sheet

问题:

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

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.

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.

回答1:

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

回答2:

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