VBA-PIVOT TABLE WIZARD METHOD

Question

I am trying to create a macro to build pivottable using the code at the following website:

http://msdn.microsoft.com/en-us/library/office/hh243933.aspx

but I kept getting

Error 1004: "UNABLE TO GET THE PIVOTFIELDS PROPERTY OF THE PIVOT TABLE CLASS"

Any suggestions on the reason behind this problem and possible ways to fix it?

THIS IS MY CODE:

Sub CreatePivot()

' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.

Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("Sheet1").Select
Range("A2").Select

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard

' Specify row and column fields.
***Set objField = objTable.PivotFields("v1")*** ' <-- This where I get the Error
objField.Orientation = xlColumnField

Set objField = objTable.PivotFields("Temperature")
objField.Orientation = xlRowField

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("clkui")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"

' Specify a page field.
Set objField = objTable.PivotFields("db")
objField.Orientation = xlPageField

' Preview the new PivotTable report.
ActiveSheet.PrintPreview

' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
    ActiveSheet.Delete
End If
Application.DisplayAlerts = True

End Sub

enter image description here

is the PivotTable already created in "Sheet1" ? or not ? where does the data for the PivotTable lies ? which Sheet ? Range ? — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2017-04-26T11:48:35

Try the code below to create a new table in Sheet1 (not sure what's the sheet's name) from the data in "Sheet1" :

Option Explicit

Sub CreatePivot()

' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.

Dim objTable As PivotTable
Dim objTblCache As PivotCache
Dim objField As PivotField
Dim PvtSht As Worksheet
Dim LastRow As Long, LastCol As Long
Dim SrcRng  As Range

' Select the sheet and first cell of the table that contains the data
With ThisWorkbook.Sheets("Sheet1")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column

    Set SrcRng = .Range(.Cells(2, "A"), .Cells(LastRow, LastCol)) ' <-- set the Pivot's dynamic Range (starts from "A2")
End With

' Option 1: Define Pivot Cache
Set objTblCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SrcRng)

' Option 2: Define Pivot Cache
Set objTblCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcRng.Address(True, True, xlA1, True))

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTables.Add(PivotCache:=objTblCache, TableDestination:=Sheet1.Cells(1, LastCol + 2), TableName:="PivotTable1")

' Specify row and column fields.
With objTable
    .PivotFields("v1").Orientation = xlColumnField
    .PivotFields("Temperature").Orientation = xlRowField

    ' Specify a data field with its summary
    ' function and format.
    Set objField = .PivotFields("clkui")
    With objField
        .Orientation = xlDataField
        .Function = xlSum
        .NumberFormat = "$ #,##0"
    End With

    ' Specify a page field.
    .PivotFields("db").Orientation = xlPageField
End With

' rest of your code goes here ....


End Sub

VBA-PIVOT TABLE WIZARD METHOD

1 Answers