98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Pivot Table Report Filter VBA Macro
Pivot Table Report Filter VBA Macro
Resolved · Low Priority · Version 2010
Thu 22 Jun 2017, 14:20Delegate Steph said...
Steph has attended:
Excel VBA Introduction course
Pivot Table Report Filter VBA Macro
Hi,
I am trying to create a macro which produces specific pivot tables. In these Pivot tables there are report filters in which I want to remove all the "0" data.
The first time I recorded the macro it has worked fine and will do the correct filter however when I do a second filter on the same data field using the same code it comes up with an error.
Does anyone know why this could be?
Kind regards
Steph
Fri 23 Jun 2017, 11:18Trainer Jens said...
RE: Pivot Table Report Filter VBA Macro
Hi Steph,
Thank you for the forum question.
You have many limitations recording PivotTable macros. I will not be able to help you with getting more information.
What is the error message?
Please copy the line in the code that course the error and let me see it.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 27 Jun 2017, 13:34Delegate Steph said...
RE: 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
Tue 27 Jun 2017, 18:33Trainer Jens said...
RE: Pivot Table Report Filter VBA Macro
Hi Steph,
Please tell me the error you get from the code. In the debug dialog box there is an error number and description. When you click Debug Excel highlight the line in the code which cause the error.
Please tell me which line Excel highlights.
I cannot test the code with the source data.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Shared Conditional FormattingIn a shared workbook, conditional formats applied before the workbook was shared will continue to work; however you cannot modify the existing conditional formats or apply new ones. |
