Formatting Pivot Tables in VBA - needs speeding up

[RobE]

I have created a macro that formats my pivot table.
I've tried to keep this generic as the fields will change to allow user to view data by month, year, etc but the data layout will always be the same (ie. Revenue, units, budget etc)
Therefore as the pivot data changes I can always refort it using this macro to ensure its easy to read. This includes some VBA to apply conditional formats where a cell contains numbers.

However its really slow - mainly it seems because each time a caption or number format is changed the status bar shows it is 'Calculating Pivot Table Report' - which it therefore does about 8-9 times

Is there any way to swith off this recalc or generally to speed this code up.

Code be

Sub format()
'
' This Macro is designed to reformat a standard pivot table (standard layout) by setting the number formats, labels, widths etc
' Macro recorded 25/11/2005 by
'

    Dim PT As PivotTable
    Dim PFNAME As String
    Dim x As Long, lastrow As Long
    
    Dim SR As String
    Set PT = ActiveSheet.PivotTables("Masterpivot")
    SR = ActiveSheet.Range("a1").Value
    lastrow = Range("G65536").End(xlUp).Row
    
   Application.ScreenUpdating = False
   Application.EnableEvents = False

    With PT
        'set the number formats
        
        .DataFields(1).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(2).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(3).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(4).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(5).NumberFormat = "0%"
        .DataFields(6).NumberFormat = "0.00%"
        .DataFields(7).NumberFormat = "0.00%"
         
        'change the captions
        .DataFields(1).Caption = " " & SR & " Revenue"
        .DataFields(2).Caption = " " & SR & " Budget Rev"
        .DataFields(3).Caption = " " & SR & " Units"
        .DataFields(4).Caption = " " & SR & " Bud Units"
        .DataFields(5).Caption = " " & SR & " % Bud Rev"
        .DataFields(6).Caption = "YoY Rev Growth"
        .DataFields(7).Caption = "YoY Unit Grow"
        
        'resize the columns and end
        Columns("C:I").EntireColumn.AutoFit
        Range("A1").Select
          
    End With
        
        'set the conditional format
            For x = 1 To lastrow
                Cells(x, "G").Activate
                    Selection.FormatConditions.Delete
                    If IsEmpty(Cells(x, "G")) Then
                    GoTo Endy
                End If
        
                If IsNumeric(Cells(x, "G").Value) Then
                    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="1"
                    Selection.FormatConditions(1).Interior.ColorIndex = 4
                    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="0.9", Formula2:="1"
                    Selection.FormatConditions(2).Interior.ColorIndex = 6
                    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0.9"
                    Selection.FormatConditions(3).Interior.ColorIndex = 3
                End If
    
Endy:
            Next x
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Thanks
Robert

 

[Norie]

Robert

Try turning off calculation.

Also you don't need all to select/activate ranges to work with them.

Sub format()
Dim PT As PivotTable
Dim rng As Range
Dim PFNAME As String
Dim x As Long, lastrow As Long
Dim SR As String
    
    Set PT = ActiveSheet.PivotTables("Masterpivot")
    SR = ActiveSheet.Range("a1").Value
    lastrow = Range("G65536").End(xlUp).Row
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    With PT
        'set the number formats
        
        .DataFields(1).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(2).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(3).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(4).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        .DataFields(5).NumberFormat = "0%"
        .DataFields(6).NumberFormat = "0.00%"
        .DataFields(7).NumberFormat = "0.00%"
        
        'change the captions
        .DataFields(1).Caption = " " & SR & " Revenue"
        .DataFields(2).Caption = " " & SR & " Budget Rev"
        .DataFields(3).Caption = " " & SR & " Units"
        .DataFields(4).Caption = " " & SR & " Bud Units"
        .DataFields(5).Caption = " " & SR & " % Bud Rev"
        .DataFields(6).Caption = "YoY Rev Growth"
        .DataFields(7).Caption = "YoY Unit Grow"
        
        'resize the columns and end
        Columns("C:I").EntireColumn.AutoFit
        Range("A1").Select
        
    End With
    
    'set the conditional format
    For x = 1 To lastrow
        Set rng = Cells(x, "G")
        rng.FormatConditions.Delete
        If Not IsEmpty(Cells(x, "G")) Then
        
            If IsNumeric(Cells(x, "G").Value) Then
                rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="1"
                rng.FormatConditions(1).Interior.ColorIndex = 4
                rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="0.9", Formula2:="1"
                rng.FormatConditions(2).Interior.ColorIndex = 6
                rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0.9"
                rng.FormatConditions(3).Interior.ColorIndex = 3
            End If
        End If
    Next x
        
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

 

[RobE]

I tried turning of f calculation but the pivot info disappears

The changes in the conditional format are faster but this isnt the slow bit
Its the fact that the pivot report seems to recalculate even though I'm only changing the number format or field caption.
I'm assuming its not actually re-calculating the sheet (as the pivot table is based on an Access db with 161K lines) but the pivot itself is re-drawing or something
I'd like to switch that off or change the formats, captions in a more efficient way

Thanks
Robert