Pivot Table Report Filter VBA Macro

Sub SetUpPivots()
'
' SetUpPivots Macro
' Sets up all the Pivots needed for Monthly MI
'

'
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Name = "Pivots"
Range("C8").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Report!R5C1:R2709C44", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Pivots!R7C3", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(7, 3).Select

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance ", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Credit Risk Rating at Origination")
.Orientation = xlRowField
.Position = 1
End With

'
' SetUpPivots2 Macro
' Continuing Pivot Set Up
'

'
Range("C3").Select
ActiveCell.FormulaR1C1 = "1. Loan Book Split by CRR"
Range("H3").Select
ActiveCell.FormulaR1C1 = "2. Reason for Arrears"
Range("H6").Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Report!R5C1:R2710C44", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Pivots!R7C8", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(6, 8).Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Current Gross Loan Balance "), _
"Count of Current Gross Loan Balance ", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Current Arrears"), "Count of Current Arrears", _
xlCount
With ActiveSheet.PivotTables("PivotTable2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of Current Arrears")
.Caption = "Sum of Current Arrears"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Count of Current Gross Loan Balance ")
.Caption = "Sum of Current Gross Loan Balance "
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reason for Arrears")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Current Months In Arrears")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Current Months In Arrears") _
.CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Current Months In Arrears")
.PivotItems("0").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Current Months In Arrears") _
.EnableMultiplePageItems = True

'
' SetUpPivots3 Macro
'

'Pivot 3

Range("N3").Select
ActiveCell.FormulaR1C1 = "3. Expected length in arrears"
Range("N7").Select
ActiveWorkbook.Worksheets("Pivots").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="Pivots!R7C14", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(7, 14).Select
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance ", xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Expected Length of Arrears ")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Current Months In Arrears")
.Orientation = xlPageField
.Position = 1
End With

'Pivot 4

Range("S3").Select
ActiveCell.FormulaR1C1 = "4. Arrears by interest type"
Range("S7").Select
ActiveWorkbook.Worksheets("Pivots").PivotTables("PivotTable3").PivotCache. _
CreatePivotTable TableDestination:="Pivots!R7C19", TableName:="PivotTable4" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(7, 19).Select

ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance ", xlSum
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Interest Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Current Months In Arrears")
.Orientation = xlPageField
.Position = 1
End With

'Pivot 5
Range("X3").Select
ActiveCell.FormulaR1C1 = "5. Arrears by Status Type"
Range("X7").Select
ActiveWorkbook.Worksheets("Pivots").PivotTables("PivotTable4").PivotCache. _
CreatePivotTable TableDestination:="Pivots!R7C24", TableName:="PivotTable5" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(7, 24).Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Status Unit Type")
.Orientation = xlRowField
.Position = 1
End With
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance ", xlSum
With ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"Current Months In Arrears")
.Orientation = xlPageField
.Position = 1
End With

'
' SetUpPivot4 Macro
'

'Pivot 6

Range("AE3").Select
ActiveCell.FormulaR1C1 = "6. Current Month in Arrears"
Range("AE7").Select
ActiveWorkbook.Worksheets("Pivots").PivotTables("PivotTable5").PivotCache. _
CreatePivotTable TableDestination:="Pivots!R7C31", TableName:="PivotTable8" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(7, 31).Select
ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance ", xlSum
ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
"PivotTable8").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance 2", xlSum
With ActiveSheet.PivotTables("PivotTable8").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Sum of Current Gross Loan Balance ")
.Caption = "Count of Current Gross Loan Balance "
.Function = xlCount
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Current Months In Arrears")
.Orientation = xlRowField
.Position = 1
End With

'Pivot 7

Range("AL3").Select
ActiveCell.FormulaR1C1 = "7. Loan Book by Region"
Range("AL7").Select
ActiveWorkbook.Worksheets("Pivots").PivotTables("PivotTable8").PivotCache. _
CreatePivotTable TableDestination:="Pivots!R7C38", TableName:="PivotTable9" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Pivots").Select
Cells(7, 38).Select
With ActiveSheet.PivotTables("PivotTable9").PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
"PivotTable9").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance ", xlSum
ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
"PivotTable9").PivotFields("Current Gross Loan Balance "), _
"Sum of Current Gross Loan Balance 2", xlSum
With ActiveSheet.PivotTables("PivotTable9").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable9").PivotFields( _
"Sum of Current Gross Loan Balance ")
.Calculation = xlPercentOfTotal
.NumberFormat = "0.00%"
End With
Range("AM42").Select
End Sub