Hey Experts,
I have written one code which basically generates the expense analysis of the expenses incurred during the month.
This code is working perfectly fine but it slow than my expectation. Is there any way to speed up that
I will explain the logics of the code and what its designed to do:
I have a pivot table in Sheet with name "Expense Analysis" with Pivot Table named "PivotTable2"
Once the Month has been entered in the combobox used in the userfrom. Pivot table gets refreshed and clears the pivot cache to exclude items which no longer exists.
I want to select all the expense categories which are part of the pivot once its refresed (There could be new categories been added everyday) except two Fund transfer and where the expense category is blank.
In the end expense catergory is sorted as A to Z
Forgot to mention two important things i guess
1. i am using Excel 2010
2. and the layout of pivot table is that if first filters data according to the month and then throws up a pivot table the expenses category wise sum of expenses incurred
I have written one code which basically generates the expense analysis of the expenses incurred during the month.
This code is working perfectly fine but it slow than my expectation. Is there any way to speed up that
I will explain the logics of the code and what its designed to do:
I have a pivot table in Sheet with name "Expense Analysis" with Pivot Table named "PivotTable2"
Once the Month has been entered in the combobox used in the userfrom. Pivot table gets refreshed and clears the pivot cache to exclude items which no longer exists.
I want to select all the expense categories which are part of the pivot once its refresed (There could be new categories been added everyday) except two Fund transfer and where the expense category is blank.
In the end expense catergory is sorted as A to Z
Code:
Private Sub CommandButtonGenerate_Click()
Sheets("Expense Analysis").Select
Dim Pvt As PivotTable
Dim Pf As PivotField
Dim Pi As PivotItem
Set Pvt = ActiveSheet.PivotTables("PivotTable2")
Set Pf = Pvt.PivotFields("Expense Category")
Range("B3").Select
Pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Pvt.PivotCache.Refresh
Pvt.PivotFields("Month").CurrentPage = Me.ComboBoxMonth.Value
With Pf
For Each Pi In .PivotItems
On Error Resume Next
Pi.Visible = True
On Error GoTo 0
Next Pi
.PivotItems("Fund Transfer").Visible = False
.PivotItems("(blank)").Visible = False
End With
Pf. _
AutoSort xlAscending, "Expense Category"
Unload Me
End SubForgot to mention two important things i guess
1. i am using Excel 2010
2. and the layout of pivot table is that if first filters data according to the month and then throws up a pivot table the expenses category wise sum of expenses incurred
Last edited:
