CODEWITHSUNDEEP
vbaexcelpivot-table
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Pivot table on the same worksheet with the data

I have created various Pivot Tables but only in other worksheets (inserting new ones) from data. Now, I have the data in the same excel sheet I want to create the Pivot Table. It keeps crushing saying it has problem in the line where I set the PCache. I am providing the code below

Sub a()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

Set PSheet = ActiveSheet

LastRow = PSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = PSheet.Cells(1, 1).Resize(LastRow, LastCol)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable TableDestination:=PSheet.Cells(2, 13), TableName:="PivotTable1"

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Destination")
    .Orientation = xlRowField
    .Position = 1
    .Subtotals(1) = True
    .Subtotals(1) = False
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total trucks")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlSum
    .Name = "Sum of Quantity (cases/sw)"
End With

'PTable.LayoutRowDefault = xlTabularRow
'Range("M2").Value = "Commodity Code"

End Sub

Upvotes: 3

Views: 140

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Read the code below and explanation inside the code's comments:

Option Explicit

Sub a()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

Set PSheet = ActiveSheet

With PSheet
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set PRange = .Cells(1, 1).Resize(LastRow, LastCol)
End With

' first: set the Pivot Cache object
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(False, False, xlA1, xlExternal))

' Second: set the Pivot Table object
Set PTable = PSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=PSheet.Cells(2, 13), TableName:="PivotTable1")

' after we set the Pivot-Table object, use the object to modify it's properties
With PTable
    With .PivotFields("Destination")
        .Orientation = xlRowField
        .Position = 1
        .Subtotals(1) = True
        .Subtotals(1) = False
    End With

    With .PivotFields("Total trucks")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .Name = "Sum of Quantity (cases/sw)"
    End With
End With

'PTable.LayoutRowDefault = xlTabularRow
'Range("M2").Value = "Commodity Code"

End Sub

Upvotes: 0

Nikolaos Polygenis
Nikolaos Polygenis

Reputation: 691

Please Replace these lines :

Set PCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 13), TableName:="PivotTable")

Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")

With:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable TableDestination:= _
PSheet.Cells(2, 13), TableName:="PivotTable1"

And Rename TableName to PivotTable1

Upvotes: 2

Related Questions