PivotTable not being created

[Olio1225]

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


'Declare Variables
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("Data")

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)


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

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

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("AlertGroup")
.Orientation = xlRowField
.Position = 1
End With


I don't get any error but it just creates the sheet "PivotTable", but no Pivot Table is created on the sheet. Can anyone help? I want the table to use all used cells on the data sheet.

 

[Alex Blakenburg]

It looks like you are not using Option Explicit at the start of your code so it is not telling you of some errors.

Try changing your Set PCache line to this below
you are missing the colon ":" on the SourceType and SourceData lines

PS: Please use the VBA button when posting code, it makes it much easier for us to help you.

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