Adding datafields to Pivot Table in VBA

[ehpomme]

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:

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:

    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

 

[colbymack]

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?

 

[ehpomme]

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?

 

[ehpomme]

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.

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

 

[ehpomme]

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.

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