VBA Pivot Table On New Sheet

Question

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.

I did indeed try that and while it did create a new sheet, the table doesn't end up in it. — Nicholas Brandon
– Nicholas Brandon, Commented Dec 6, 2013 at 1:53

user2140261 · Accepted Answer · 2013-12-06 01:52:59Z

4

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

answered Dec 6, 2013 at 1:52

user2140261

8,0037 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nicholas Brandon Over a year ago

Oh my goodness, I have spent literally 4 hours trying to figure out this one thing and you come along and solve it just like that. Thank you SO much. You're amazing. One last thing, sorry, the name "PivotTableName" is that a general form to cause you to not need a specific name, or could you put anything in there?

user2140261 Over a year ago

@NicholasBrandon You can use whatever you want the name of the pivot table to be

Riya Sen · Accepted Answer · 2014-11-03 06:01:40Z

1

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

edited Nov 3, 2014 at 6:01

answered Nov 3, 2014 at 5:55

Riya Sen

112 bronze badges

1 Comment

Riya Sen Over a year ago

its runs successfully.. enjoy guys :)

Collectives™ on Stack Overflow

VBA Pivot Table On New Sheet

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related