Creating Pivot Table by VBA

[intern1]

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.

 

[Domenic]

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

TableDestination:=Range("A3")

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

    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!

 

[intern1]

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?

 

[Domenic]

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

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

And the alternative would be as follows...

    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?

 

[intern1]

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