Creating Pivot Table with code

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi,

Can you post a bit more of the code as it's hard to tell from there what's wrong.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.......
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top