Excel VBA to create Pivot, no table appearing

Question

The VBA code I have below is trying to create a Pivot Table based on data in "Sheet2", adding a new sheet "Pivot Table" and creating a Pivot Table on this sheet.

However the code is executing without error, just no table appears on the new sheet "Pivot Table" and I cannot see why.

Also the data on Sheet2 for the PT currently begins in column B, not sure if this is having an effect.

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


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sheet2")



'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)


'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="MilestonePivotTable")



'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="MilestonePivotTable")



'Insert Row Fields
With ActiveSheet.PivotTables("MilestonePivotTable").PivotFields("Resource Name")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("MilestonePivotTable").PivotFields("Deliverable")
.Orientation = xlRowField
.Position = 2
End With

'Insert Column Fields
With ActiveSheet.PivotTables("MilestonePivotTable").PivotFields("Milestone Date")
.Orientation = xlColumnField
.Position = 1
End With

If your data starts in column B, shouldn't PRange be set as Set PRange = DSheet.Cells(1, "B").Resize(LastRow, LastCol - 1)? — YowE3K
First try fully qualifying your Rows and Columns part where you find the last row by prefixing them with DSheet. It will be using ActiveSheet by default, which can cause problems. — braX
@braX Because PSheet, DSheet and ActiveSheet are all in the same workbook, an unqualified Rows.Count and Columns.Count won't cause an issue. (But it is definitely a good idea to qualify them anyway!) — YowE3K
Place an On Error GoTo 0 after your Application.DisplayAlerts = True, so that errors aren't masked after that - it will then crash when the errors occur. — YowE3K

YowE3K YowE3K · Accepted Answer · 2017-11-20T20:48:36

Only semi-tested (due to only having dummy data) but I believe the following will fix all your errors:

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
'LastRow was not declared
Dim LastRow As Long
'LastRow1 is not used
'Dim LastRow1 As Long
Dim LastCol As Long


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
'Switch off error "masking" once you don't need it
On Error GoTo 0
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Sheet2")



'Define Data Range
'a) Base last row on column B if you don't have data in column A
'b) Use "DSheet.Rows.Count" and "DSheet.Columns.Count" - although not strictly
'   necessary in this situation, it is a good practice to get into
LastRow = DSheet.Cells(DSheet.Rows.Count, "B").End(xlUp).Row
LastCol = DSheet.Cells(1, Dsheet.Columns.Count).End(xlToLeft).Column
'Don't include column A in your data range
Set PRange = DSheet.Cells(1, "B").Resize(LastRow, LastCol - 1)


'Define Pivot Cache (not pivot table)
Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)
'Set PCache = ActiveWorkbook.PivotCaches.Create _
'(SourceType:=xlDatabase, SourceData:=PRange). _
'CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
'TableName:="MilestonePivotTable")



'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 1), TableName:="MilestonePivotTable")


'Use PSheet instead of ActiveSheet in all the following statements
'In fact, because the PTable object exists, we can just use it

'Insert Row Fields
'With AciveSheet.PivotTables("MilestonePivotTable").PivotFields("Resource Name")
'With PSheet.PivotTables("MilestonePivotTable").PivotFields("Resource Name")
With PTable.PivotFields("Resource Name")
    .Orientation = xlRowField
    .Position = 1
End With

With PTable.PivotFields("Deliverable")
    .Orientation = xlRowField
    .Position = 2
End With

'Insert Column Fields
With PTable.PivotFields("Milestone Date")
    .Orientation = xlColumnField
    .Position = 1
End With

Excel VBA to create Pivot, no table appearing

1 Answers