Error while creating Pivot Table automatically

Question

I have created a macro to create a table with some values via Index matches and general calculations. This table is located on the range("AA1:BA"&LastRow) --> I am writing LastRow because the lastrow changes daily. I want to create a pivot table which will be located in the cell(9,1) and will have as a source the initial table that I created.. I built the code that you see below, but it gives me an error saying that the PivotTable field name is not valid on the line :

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

The entire code related to the Pivot Table is below.

   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

Application.DisplayAlerts = False
Application.DisplayAlerts = True
Set PSheet = Worksheets("Budget_Report")
Set DSheet = Worksheets("Budget_Report")

LastRow = DSheet.Cells(Rows.Count, 27).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set PRange = DSheet.Cells(1, 27).Resize(LastRow, LastCol)

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(9, 1), TableName:="PivotTable")

With ActiveSheet.PivotTables("PivotTable").PivotFields("T-Lane")
.Orientation = xlRowField
.Position = 1
.Subtotals(1) = True
.Subtotals(1) = False
End With

With ActiveSheet.PivotTables("PivotTable").PivotFields("Monthly Cost FCST")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.Name = "Monthly Cost Forecast"
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Monthly Vol FCST")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.Name = "Monthly Vol Forecast"
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Monthly $/SU FCST")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
.Name = "Monthly $/SU Forecast"
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Monthly Cost Actuals")
.Orientation = xlDataField
.Position = 4
.Function = xlSum
.Name = "Monthly Cost Actual"
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Monthly Vol Actuals")
.Orientation = xlDataField
.Position = 5
.Function = xlSum
.Name = "Monthly Vol Actual"
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Monthly $/SU Actuals")
.Orientation = xlDataField
.Position = 6
.Function = xlSum
.Name = "Monthly $/SU Actual"
End With

The recorded macro that builds the Pivot Table is provided below (it has specific range not lastrow and lastcolumn of course)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Budget_Report!R1C27:R279C53", Version:=6).CreatePivotTable TableDestination:="Budget_Report!R9C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Budget_Report").Cells(9, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("T-Lane")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Monthly Cost FCST"), "Monthly Cost Forecast", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Monthly Vol FCST"), "Monthly Vol Forecast", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Monthly $/SU FCST"), "Monthly $/SU Forecast", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Monthly Cost Actuals"), "Monthly Cost Actual", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Monthly Vol Actuals"), "Monthly Vol Actual", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Monthly $/SU Actuals"), "Monthly $/SU Actual", xlSum

ActiveWorkbook.ShowPivotTableFieldList = False

what would be the field names of the pivot table if there was no error? — jsotola
hmm I am sorry jsotola but I dont really understand the question.. I am not very experienced with Pivot Tables so I cannot really answer the question :( I just saw the code online and tried to implement it for my macro . — Pericles Faliagas
do you actually have data that can be fed into a pivot table? ... if you do, then create the pivot table manually. excel should complain about the field name and it may point you to the problem. — jsotola
I just created the pivot table manually and it has no issue.. Could it be because I am naming the Pivot Table in the line where I set the PTable? — Pericles Faliagas
create the pivot table again, but this time start the macro recorder before you do the creation. then check the resulting code — jsotola

Chris Meurer Chris Meurer · Accepted Answer · 2017-10-17T20:52:49

When setting the PRange you can use Cells.SpecialCells method to set your range even though you don't always know what that end address will be as long as you're sure you won't have extra data somewhere below/beyond your target range.

It would look something like this:

Set PRange = DSheet.Range(DSheet.Cells(1, 27), DSheet.Cells.SpecialCells(xlCellTypeLastCell))

Also, it is a matter of personal style, but I find that nesting With blocks sometimes results in more readable code. Eg:

With ActiveSheet.PivotTables("PivotTable1")
    With .PivotFields("Field 1")
        .Caption = "My first field"
        .Function = xlSum
    End With
    With .PivotFields("Field 2")
        .Caption = "My second field"
        .Function = xlMax
    End With
End With

etc.

The final product looks something like this:

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

'Application.DisplayAlerts = False   ' This is redundant since you're setting it to true below.
Application.DisplayAlerts = True
'Set PSheet = Worksheets("Budget_Report") '
Set DSheet = Worksheets("Budget_Report") ' These two are Redundant - I am using only DSheet.

'LastRow = DSheet.Cells(Rows.Count, 27).End(xlUp).Row
'LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set PRange = DSheet.Range(Cells(1, 27),Cells.SpecialCells(xlCellTypeLastCell))

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=DSheet.Cells(9, 1), TableName:="PivotTable")

With ActiveSheet.PivotTables("PivotTable")
 With .PivotFields("T-Lane")
    .Orientation = xlRowField
    .Position = 1
    .Subtotals(1) = True   ' You're resetting this value in the next     line, you can probably remove it.
    .Subtotals(1) = False
 End With

With .PivotFields("Monthly Cost FCST")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.Caption = "Monthly Cost Forecast" 'You want .Caption here, not .Name
End With

With .PivotFields("Monthly Vol FCST")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.Caption = "Monthly Vol Forecast"
End With

With .PivotFields("Monthly $/SU FCST")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
.Caption = "Monthly $/SU Forecast"
End With

With .PivotFields("Monthly Cost Actuals")
.Orientation = xlDataField
.Position = 4
.Function = xlSum
.Caption = "Monthly Cost Actual"
End With

With .PivotFields("Monthly Vol Actuals")
.Orientation = xlDataField
.Position = 5
.Function = xlSum
.Caption = "Monthly Vol Actual"
End With

With .PivotFields("Monthly $/SU Actuals")
.Orientation = xlDataField
.Position = 6
.Function = xlSum
.Caption = "Monthly $/SU Actual"
End With

End With

Error while creating Pivot Table automatically

3 Answers