Creating pivot table using vba - run time error

0
votes

I am trying to create a pivot table using vba. Following is the code that i tried:

Sub CreatePivot()

    Dim objTable As PivotTable
    Dim objField As PivotField
    Dim ws As Worksheet

    Set ws = Worksheets.Add
    Sheets(ws.Name).Name = "Write-Off Pivot"

    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("GEP Write-Offs Rawdata").Select
    Range("A1").Select

    Set objTable = ws.PivotTableWizard

    ' Specify row and column fields.
    Set objField = objTable.PivotFields("MPG")
    objField.Orientation = xlRowField

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


End Sub

I am getting this error message. "Run-time error '104'" "method 'pivottables' of object '_worksheet' failed"

The problem seems to be in this line but not able to figure what's causing it.

Set objTable = ws.PivotTableWizard
3
excelpivotvba
You seem to be missing some arguments. Have a look at the formal documentation to see if it gets you any closer: msdn.microsoft.com/en-us/library/office/ff839228.aspx – sous2817
@Yuvaraj I have submitted my answer after verification on test data with Excel 2016 and eager to have feedback from an active member like you. It is certainly not for points on answer but to enhance our learning experience as it is never ending process. – skkakkar
Your prompt action is appreciated. Thanks – skkakkar

3 Answers

1
votes

Your following line needs change.

Set objTable = ws.PivotTableWizard

This should point to data source sheet. So it should be. 

Set objTable = Sheets("GEP Write-Offs Rawdata").PivotTableWizard

It will make the pivot table and write in a new added sheet since your command 

Set ws = Worksheets.Add
    Sheets(ws.Name).Name = "Write-Off Pivot"

is not linked to it.May please modify it suitably.

This is in addition to what @Rory suggested.

0
votes

You need to use Use ActiveSheet.Pivottablewizard rather than ws.PivotTableWizard since there isn't any data on the ws sheet.

Incidentally, this is unnecessarily convoluted:

Sheets(ws.Name).Name = "Write-Off Pivot"

All you need is:

ws.Name = "Write-Off Pivot"
0
votes

The methods PivotTableWizard needs some parameters:

Like this:

 Set objTable = ws.PivotTableWizard(xlDatabase, ActiveWorkbook.Worksheets("Table8").Range("A1:A5"), ws.Range("A1:A6"))

There are a lot of parameters, for your example i needed the datasource type, the datasource and the datadestination.

You see all parameters if you just open the "(" after es.PivotTableWizard or you can get them here:

https://msdn.microsoft.com/de-de/library/office/ff839228.aspx