Help with: Pivot Table Code - Adding fields effectively

[Skjoldborg]

Hi Experts !

I am processing a quite large macro for several worksheets, on each worksheet I end up creating a pivot table. The code works fine, except that it is not running fast, and I think optimizing the pivot is a good place to start. What I have now working is this:

(Excel 2007)

'the GName variable is a string with the name of the sheet 
'and here also the name of the Pivot

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Worksheets(GName).Range("A53").CurrentRegion, Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:=ActiveSheet.Cells(77, 3), TableName:=GName, DefaultVersion:= _
    xlPivotTableVersion12

    
    Sheets(GName).Select
    Cells(77, 3).Select
    With ActiveSheet.PivotTables(GName).PivotFields("Business Area")
        .Orientation = xlRowField
        .Position = 1
    End With
  
    With ActiveSheet.PivotTables(GName)
    .AddDataField .PivotFields("Q1, 2003"), "Sum of Q1, 2003", xlSum
    .AddDataField .PivotFields("Q2, 2003"), "Sum of Q2, 2003", xlSum
    .AddDataField .PivotFields("Q3, 2003"), "Sum of Q3, 2003", xlSum
    .AddDataField .PivotFields("Q4, 2003"), "Sum of Q4, 2003", xlSum
    .AddDataField .PivotFields("Q1, 2004"), "Sum of Q1, 2004", xlSum
    .AddDataField .PivotFields("Q2, 2004"), "Sum of Q2, 2004", xlSum
    .AddDataField .PivotFields("Q3, 2004"), "Sum of Q3, 2004", xlSum
    .AddDataField .PivotFields("Q4, 2004"), "Sum of Q4, 2004", xlSum
    .AddDataField .PivotFields("Q1, 2005"), "Sum of Q1, 2005", xlSum
    .AddDataField .PivotFields("Q2, 2005"), "Sum of Q2, 2005", xlSum
    .AddDataField .PivotFields("Q3, 2005"), "Sum of Q3, 2005", xlSum
    .AddDataField .PivotFields("Q4, 2005"), "Sum of Q4, 2005", xlSum
    .AddDataField .PivotFields("Q1, 2006"), "Sum of Q1, 2006", xlSum
    .AddDataField .PivotFields("Q2, 2006"), "Sum of Q2, 2006", xlSum
    .AddDataField .PivotFields("Q3, 2006"), "Sum of Q3, 2006", xlSum
    .AddDataField .PivotFields("Q4, 2006"), "Sum of Q4, 2006", xlSum
    .AddDataField .PivotFields("Q1, 2007"), "Sum of Q1, 2007", xlSum
    .AddDataField .PivotFields("Q2, 2007"), "Sum of Q2, 2007", xlSum
    .AddDataField .PivotFields("Q3, 2007"), "Sum of Q3, 2007", xlSum
    .AddDataField .PivotFields("Q4, 2007"), "Sum of Q4, 2007", xlSum
    .AddDataField .PivotFields("Q1, 2008"), "Sum of Q1, 2008", xlSum
    .AddDataField .PivotFields("Q2, 2008"), "Sum of Q2, 2008", xlSum
    .AddDataField .PivotFields("Q3, 2008"), "Sum of Q3, 2008", xlSum
    .AddDataField .PivotFields("Q4, 2008"), "Sum of Q4, 2008", xlSum
    .AddDataField .PivotFields("Q1, 2009"), "Sum of Q1, 2009", xlSum
    .AddDataField .PivotFields("Q2, 2009"), "Sum of Q2, 2009", xlSum
    .AddDataField .PivotFields("Q3, 2009"), "Sum of Q3, 2009", xlSum
    .AddDataField .PivotFields("Q4, 2009"), "Sum of Q4, 2009", xlSum
    .AddDataField .PivotFields("Q1, 2010"), "Sum of Q1, 2010", xlSum
    .AddDataField .PivotFields("Q2, 2010"), "Sum of Q2, 2010", xlSum
    .AddDataField .PivotFields("Q3, 2010"), "Sum of Q3, 2010", xlSum
    .AddDataField .PivotFields("Q4, 2010"), "Sum of Q4, 2010", xlSum
    .AddDataField .PivotFields("Q1, 2011"), "Sum of Q1, 2011", xlSum
    .AddDataField .PivotFields("Q2, 2011"), "Sum of Q2, 2011", xlSum
    .AddDataField .PivotFields("Q3, 2011"), "Sum of Q3, 2011", xlSum
    .AddDataField .PivotFields("Q4, 2011"), "Sum of Q4, 2011", xlSum
    .AddDataField .PivotFields("Q1, 2012"), "Sum of Q1, 2012", xlSum
    .AddDataField .PivotFields("Q2, 2012"), "Sum of Q2, 2012", xlSum
    .AddDataField .PivotFields("Q3, 2012"), "Sum of Q3, 2012", xlSum
    .AddDataField .PivotFields("Q4, 2012"), "Sum of Q4, 2012", xlSum
    .AddDataField .PivotFields("Q1, 2013"), "Sum of Q1, 2013", xlSum
    .AddDataField .PivotFields("Q2, 2013"), "Sum of Q2, 2013", xlSum
    .AddDataField .PivotFields("Q3, 2013"), "Sum of Q3, 2013", xlSum
    .AddDataField .PivotFields("Q4, 2013"), "Sum of Q4, 2013", xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False

any advice on optimizing is appreciated - thx

 

[Hermanito]

untested, might contain some errors still:

'the GName variable is a string with the name of the sheet 
'and here also the name of the Pivot

Dim YearValues As Integer
Dim Quarters As Integer
Dim TempString As String

With ThisWorkbook.Worksheets(GName)
   ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ 
      SourceData:= .Range("A53").CurrentRegion, _
      Version:=xlPivotTableVersion12).CreatePivotTable _
      TableDestination:=.Cells(77, 3), TableName:=GName, _
      DefaultVersion:= xlPivotTableVersion12
   With .PivotTables(GName).PivotFields("Business Area")
        .Orientation = xlRowField
        .Position = 1
   End With

   With .PivotTables(GName)
      For YearValues = 2003 To 2013
         For Quarters = 1 To 4
            TempString = "Q" & Trim(Str(Quarters)) & ", " & Trim(Str(YearValues))
           .AddDataField .PivotFields(TempString), "Sum of " & TempString, xlSum
         Next Quarters
      Next YearValues
   End With   
.ShowPivotTableFieldList = False
End With

I removed all Select statements as those can slow things down. For the rest I just made it a bit more concise by putting all those similar statements in a nested loop...

 

[RoryA]

I would also set the PivotTable's ManualUpdate property to True at the start and back to False at the end.

 

[Skjoldborg]

Hermanito,
Your code works like a charm! Brilliant!!
I knew that it could be made a lot prettier

Thank you very much.

/Søren

 

[Skjoldborg]

I would also set the PivotTable's ManualUpdate property to True at the start and back to False at the end.

Rory,
Thank you for the advice. I am not familiar with how to use the ManualUpdate property.. does the below look correct?

in the beginning of my macro i would insert following code:

<CODE>Worksheets(GName).PivotTables(GName).ManualUpdate = True</CODE></PRE>
<CODE>

and then at the end of the macro i would write

<CODE>Worksheets(GName).PivotTables(GName).ManualUpdate = False</CODE>

<CODE>

</CODE></PRE>
Would I have to make a refresh of the pivot?
</CODE></PRE>

 

[RoryA]

Correct code. You only need to refresh the table if you change the data after setting the ManualUpdate to True.