Creating Pivot Table with code

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,471
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,471
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,471
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,471

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,471
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,917
Members
410,711
Latest member
Josh324
Top