Creating Pivot Table with code

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
I have recorded a macro to create a pivot table and then copied the code into a macro but it seems to be falling down on the following line
Code:
ActiveSheet.PivotTables("Jan06").AddFields RowFields:=Array( _
        "Job Number description", "Identifier"), ColumnFields:="Activity Description"
Not sure why as it works OK when recording.

Any ideas anyone?

Thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Can you post a bit more of the code as it's hard to tell from there what's wrong.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
Here you go......
Code:
Range("1:1").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Data!C1:C13").CreatePivotTable TableDestination:="", TableName:="Jan06", _
        DefaultVersion:=xlPivotTableVersion10
        
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    
ActiveSheet.Cells(3, 1).Select
    
ActiveSheet.PivotTables("Jan06").AddFields RowFields:=Array( _
        "Job Number description", "Identifier"), ColumnFields:="Activity Description"
            
With ActiveSheet.PivotTables("Jan06").PivotFields("Total Charge")
    .Orientation = xlDataField
    .Caption = "Sum of Total Charge"
    .Function = xlSum
End With
It is falling down when trying to add the fields
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
It is coming up with the following error message -
AddFields method of PivotTable class failed
And in Excel the pivot table field list it only shows 'Activity Description' which is one of the fields I want to add to the column.

Is it not picking up all the filed in row 1? I thought if I used
Code:
Range("1:1").Select
I am in the right place to select the required fields.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490

ADVERTISEMENT

Right think I have sorted the problem, but not sure why it works.

Instead of slection columns 1-13 C1:C13 I have used A:M and it now works!!!

Excel is strange sometimes.......
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

You're probably right, have to admit I'd avoid selecting the whole row prior to creating a pivot table as it wouldn't represent the data that I wanted to analyze.

I think you may be confusing the wizard by doing so.

The rest of your code looks fine, almost identical to some I use myself although I prefer to use named ranges to define my source data.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
I think I will try it with a named range then as don't want to have any blanks in the table.

Thanks for looking anyway!
 

Forum statistics

Threads
1,141,920
Messages
5,709,349
Members
421,631
Latest member
JDamery

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top