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
PivotTable
already created in "Sheet1" ? or not ? where does the data for thePivotTable
lies ? which Sheet ? Range ? – Shai Rado