Creating pivot table using vba - run time error

Question

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

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
– sous2817, Commented Jul 12, 2016 at 14:09
@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
– skkakkar, Commented Jul 13, 2016 at 19:43

skkakkar · Accepted Answer · 2016-07-12 14:55:32Z

1

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.

answered Jul 12, 2016 at 14:55

skkakkar

2,8482 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skkakkar Over a year ago

@Rory I gather courage to request your viewpoint on change suggested by me regarding Set objTable. Since I am at beginner level in excel-vba and eager to learn, I shall have no hesitation in withdrawing my answer if this is irrelevant or wrong.

Rory · Accepted Answer · 2016-07-12 14:20:59Z

0

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"

answered Jul 12, 2016 at 14:20

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Comments

thentt · Accepted Answer · 2016-07-12 14:42:22Z

0

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

edited Jul 12, 2016 at 14:42

answered Jul 12, 2016 at 14:34

thentt

265 bronze badges

1 Comment

Rory Over a year ago

It doesn't actually need any of them in fact.

Collectives™ on Stack Overflow

Creating pivot table using vba - run time error

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related