Adding datafields to Pivot Table in VBA

ehpomme

Board Regular
Joined
Jun 17, 2004
Messages
67
Hi all,

I trying to build a macro that uses a Userform to collect information that resides on a datasheet, and then launches a pivot table to display this data. My current problem lies with trying to get the macro to add datafields to the pivot table (in this case, portfolio performance by year). As long as I specify which years to collect, the macro has no problem organizing the data. However, the number of years in this workbook is subject to change, and I'm trying to come up with a loop or something that will add these years automatically as they are added to the workbook.

This is an example of the code that's working:

Code:
ActiveSheet.PivotTables("Tableau croisé dynamique").AddDataField ActiveSheet.PivotTables("Tableau croisé dynamique").PivotFields("Performance 2004 ( % )"), "Somme de Performance 2004 ( % )", xlSum
ActiveSheet.PivotTables("Tableau croisé dynamique").AddDataField ActiveSheet.PivotTables("Tableau croisé dynamique").PivotFields("Performance 2005 ( % )"), "Somme de Performance 2005 ( % )", xlSum
ActiveSheet.PivotTables("Tableau croisé dynamique").AddDataField ActiveSheet.PivotTables("Tableau croisé dynamique").PivotFields("Performance 2006 ( % )"), "Somme de Performance 2006 ( % )", xlSum
ActiveSheet.PivotTables("Tableau croisé dynamique").AddDataField ActiveSheet.PivotTables("Tableau croisé dynamique").PivotFields("Performance 2007 ( % )"), "Somme de Performance 2007 ( % )", xlSum

This is what I'm trying to do:

Code:
    For i = 0 To étude.YearBox.ListCount

' where étude is a Userform, and YearBox is a ListBox
    
    ActiveSheet.PivotTables("Tableau croisé dynamique").AddDataField ActiveSheet.PivotTables("Tableau croisé dynamique").PivotFields(Sheets("Data").Cells(i + 2, 1)), i, xlSum
    
    Next i

Please let me know if I'm being too vague!

Thanks for all your help in advance
Marc
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Not being too advanced, can you have a cell in your spreadsheet that has the min year and another cell that has the max year and then reference the values in those cells in your macro?
 
Upvote 0
That's actually what I'm trying to do in the second 'code quote'. The 'For i = 0 ... Next' bit is trying to grab the values in cells(i, 1) as the names of the datafields. For some reason, what I have isn't working?
 
Upvote 0
So I'm trying a slightly different loop that's more cumbersome, but it might work.

I'm guessing there's a syntax issue I'm missing with the values I'm using.

Code:
Private Sub test()
Dim x As String
Dim row As Integer
       
   x = Sheets("Data").Range("a2").Value
   row = 2
   Sheets("Data").Activate
   Sheets("Data").Cells(row, 1).Select
   Do While x <> ""
        Sheets("Data").Cells(row, 1).Activate
        Sheets("Feuil1").PivotTables("Tableau croisé dynamique").AddDataField Sheets("Feuil1").PivotTables("Tableau croisé dynamique").PivotFields(x), "Somme de " & x, xlSum
        row = row + 1
        x = Sheets("Data").Cells(row, 1).Value
   Loop
End Sub

It's definitely less sexy, but it could work, I think. The problem seems to be with the line : Sheets("Feuil1").PivotTables("Tableau croisé dynamique").AddDataField Sheets("Feuil1").PivotTables("Tableau croisé dynamique").PivotFields(x), "Somme de " & x, xlSum.

It seems like everytime I try to specify the worksheet 'Data' I mess up my code. Does anyone have any suggestions?

Eric
 
Upvote 0
I got it to work using the following code. It seems to help to have the macro select the sheet you are explicitly working with.

Code:
Private Sub test()
Dim x As String
Dim row As Integer
       
   x = Sheets("Data").Range("a2").Value
   row = 2
   Sheets("Data").Activate
   Sheets("Data").Cells(row, 1).Select
   Do While x <> ""
        Sheets("Feuil1").Select
        Sheets("Feuil1").PivotTables("Tableau croisé dynamique").AddDataField Sheets("Feuil1").PivotTables("Tableau croisé dynamique").PivotFields(x), "Somme de " & x, xlSum
        row = row + 1
        Sheets("Data").Select
        x = Sheets("Data").Cells(row, 1).Value
   Loop
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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