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
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
Code:
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