Creating Pivot Table by VBA

intern1

New Member
Joined
Jun 23, 2018
Messages
9
Hello,
I'm new to VBA and macros and i get an error each time I try this. I want to create a pivot table from a table in the sheet called Training List and place it in a new sheet:I feel like the new sheet is causing the problem but I am unsure.

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"TrainingList", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6

One more thing-I have been searching for answers online, but I do not really understand the language that's used many times. That might also be part of my problem.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Since you're adding a new worksheet, I'm assuming that you want to create your pivot table in this newly created sheet. If so, change the destination as follows...

Code:
TableDestination:=Range("A3")

Here's another way to create your pivot table...

Code:
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    'Create the cache for the pivot table
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:="TrainingList")
        
    'Add a new worksheet for the pivot table
    Sheets.Add
    
    'Create the pivot table
    Set PT = ActiveSheet.PivotTables.Add( _
        PivotCache:=PTCache, _
        TableDestination:=Range("A3"), _
        TableName:="PivotTable1")

Hope this helps!
 
Last edited:
Upvote 0
Thanks for responding-the first option did not work.
I tried the code, is that supposed to replace the section I had originally included?
When I do that, I now get a different line highlighted-

Dim PTCache As PivotCache Dim PT As PivotTable

'Create the cache for the pivot table
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="TrainingList")

Range("A118").Select
Sheets.Add

'Create the pivot table
Set PT = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=Range("A3"), _
TableName:="PivotTable1")


Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable5")

I've been trying to do this my recording a macro; is it even possible to create a pivot table that way?
 
Upvote 0
I re-read your original post. It looks like the source data is not specified correctly. If the sheet name is TrainingList, and the data starts at A1, the source data should be specified like this...

Code:
SourceData:=Worksheets("TrainingList").Range("A1").CurrentRegion

And the alternative would be as follows...

Code:
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    'Create the cache for the pivot table
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("TrainingList").Range("A1").CurrentRegion)
        
    'Add a new worksheet for the pivot table
    Sheets.Add
    
    'Create the pivot table
    Set PT = ActiveSheet.PivotTables.Add( _
        PivotCache:=PTCache, _
        TableDestination:=Range("A3"), _
        TableName:="PivotTable1")

Does this help?
 
Upvote 0
It does-thanks!
I used this as well as a YouTube video I found. I ended up renaming my new sheet, creating the table there and then giving that sheet a name specific to the table. Seems like a redundant step now that I write it out, but it worked over and over.

Sub ClassperLOB()
'
' ClassperLOB Macro
' count of classes per month per LOB Year filter available
'


'
Sheets.Add After:=ActiveSheet

Sheets(ActiveSheet.Name).Name = "Newpage"
Sheets("TrainingList").Select
ActiveWorkbook.Worksheets("count of class per method").PivotTables( _
"PivotTable8").PivotCache.CreatePivotTable TableDestination:="Newpage!R1C1", _
TableName:="PivotTable4", DefaultVersion:=6
Sheets("Newpage").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable4").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable4").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Year")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Month")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("LOB")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Classes"), "Sum of Classes", xlSum
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of Classes")
.Caption = "Count of Classes"
.Function = xlCount
End With
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Newpage!$A$3:$R$18")

Sheets("Newpage").Name = "class by lob"
Range("L24").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top