CODEWITHSUNDEEP
excelvbapivot-table
radeg
radeg

Reputation: 61

How to Define a Pivot Cache?

I am writing a code that will create a pivot table based on a dynamic range. I am having issues creating the pivot cache, however. When I run the code, no error message appears, but a blank worksheet is created with no pivot table. I think the issue is the pivot cache but could be wrong. Any ideas?

I've stepped through the code several times but cannot find a bug. Everything appears to be working as it should, except no pivot table appears.

Option Explicit

Dim pivotSht As Worksheet
Dim dataSht As Worksheet
Dim pCache As PivotCache
Dim pTable As PivotTable
Dim pRange As Range
Dim lastR As Long
Dim lastC As Long

Public Sub buildPivot()

'CREATES PIVOT TABLE FROM OPEN ORDER BOOK DATA

'Deletes old sheet, if available, and creates a new one
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True

Set pivotSht = Worksheets("PivotTable")
Set dataSht = Worksheets("OOB")

'Defines data range in "OOB" sheet
lastR = dataSht.Cells(Rows.Count, "D").End(xlUp).Row
lastC = dataSht.Cells(4, Columns.Count).End(xlToLeft).Column
Set pRange = dataSht.Range("D1").Resize(lastR, lastC)

'Define pivot cache
Set pCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=pRange). _
CreatePivotTable(tabledestination:=pivotSht.Cells(3, 1), _
TableName:="OpenOrderBookTable")

'Insert blank pivot table
Set pTable = pCache.CreatePivotTable _
(tabledestination:=pivotSht.Cells(3, 1), TableName:="OpenOrderBookTable")

'Insert row fields in pivot table
With ActiveSheet.PivotTables("OpenOrderBookTable").PivotFields("Machine")
    .Orientation = xlRowField
    .Position = 1
End With

'Insert column fields in pivot table
With ActiveSheet.PivotTables("OpenOrderBookTable").PivotFields("OTD")
    .Orientation = xlColumnField
    .Position = 1
End With

'Insert data fields
ActiveSheet.PivotTables("OpenOrderBookTable").AddDataField ActiveSheet.PivotTables( _
    "OpenOrderBookTable").PivotFields("OTD"), "Count of OTD", xlCount

With ActiveSheet.PivotTables("OpenOrderBookTable").PivotFields("System Status")
    .Orientation = xlPageField
    .Position = 1
End With

I expect a pivot table with my range on a new worksheet but am not getting anything.

Upvotes: 2

Views: 217

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

Try the modified code below:

Public Sub buildPivot()

'CREATES PIVOT TABLE FROM OPEN ORDER BOOK DATA

'Deletes old sheet, if available, and creates new one
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True

Set pivotSht = Worksheets("PivotTable")
Set dataSht = Worksheets("OOB")

'Defines data range in "OOB" sheet
With dataSht
    lastR = .Cells(.Rows.Count, "D").End(xlUp).Row
    lastC = .Cells(4, .Columns.Count).End(xlToLeft).Column
    Set pRange = .Range(.Cells(1, "D"), .Cells(lastR, lastC))
End With

'Define pivot cache
Set pCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pRange.Address(False, False, xlA1, xlExternal))

'Insert blank pivot table
Set pTable = pivotSht.PivotTables.Add(PivotCache:=pCache, TableDestination:=pivotSht.Range("A3"), TableName:="OpenOrderBookTable")

'Insert row fields in pivot table
With pTable.PivotFields("Machine")
    .Orientation = xlRowField
    .Position = 1
End With

'Insert column fields in pivot table
With pTable.PivotFields("OTD")
    .Orientation = xlColumnField
    .Position = 1
End With

'Insert data fields
pTable.AddDataField pTable.PivotFields("OTD"), "Count of OTD", xlCount

With pTable.PivotFields("System Status")
    .Orientation = xlPageField
    .Position = 1
End With

End Sub

Upvotes: 2

Related Questions