pivot table report filter

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

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

resolvedResolved · Low Priority · Version 2010

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

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

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

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 courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
microsoft excel training class and other training courses.

Excel tip:

Shared Conditional Formatting

In 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.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.09 secs.