J
Justin Larson
I have a worksheet change event that has been bugging me for a while now.
When I first implemented it, it worked like a charm, but it keeps getting
slower and slower. And I mean to the tune of minutes, not seconds. I've
waited as long as 20 minutes for it to run on just the one workbook.
When I change a control cell (one of two, in this case), an associated page
field in all pivot tables on the document update to the value of the control
cell.
Here's the code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Utility"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("B1").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
strField = "Sale_Date"
If Target.Address = Range("C1").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
ws_exit:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
There are a total of 5 pivot tables, all sharing the same source, based on
data in one sheet of the workbook. There are 7 sheets total in the workbook,
and the dataset is the only notably "large" one. There is one sheet that is
100% link to an outside workbook, but doesn't have any pivot tables in it, or
any other interaction within this workbook.
There are only two things about the workbook that I can think of that make
this scenario sketchy:
1) the data in the "dataset" sheet, where all the pivot data is stored,
changes all the time. Well, actually, it's a huge dataset (12-20K rows,
column(A:AB)) that gets emptied and refilled with fresh data once a month.
2) because the dataset changes, the source that the pivot table is looking
at is a dynamic range defined as :
=OFFSET(dataset!$A$1,0,0,COUNTA(dataset!$A:$A),COUNTA(dataset!$1:$1))
Because of that, I feel like it may be some sort of caching or memory
problem, but I just can't nail it down. I've tried this and that for clearing
cache, but to be honest, I don't know if it's working. It's not making the
sheet work faster.
When I first implemented it, it worked like a charm, but it keeps getting
slower and slower. And I mean to the tune of minutes, not seconds. I've
waited as long as 20 minutes for it to run on just the one workbook.
When I change a control cell (one of two, in this case), an associated page
field in all pivot tables on the document update to the value of the control
cell.
Here's the code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Utility"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("B1").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
strField = "Sale_Date"
If Target.Address = Range("C1").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
ws_exit:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
There are a total of 5 pivot tables, all sharing the same source, based on
data in one sheet of the workbook. There are 7 sheets total in the workbook,
and the dataset is the only notably "large" one. There is one sheet that is
100% link to an outside workbook, but doesn't have any pivot tables in it, or
any other interaction within this workbook.
There are only two things about the workbook that I can think of that make
this scenario sketchy:
1) the data in the "dataset" sheet, where all the pivot data is stored,
changes all the time. Well, actually, it's a huge dataset (12-20K rows,
column(A:AB)) that gets emptied and refilled with fresh data once a month.
2) because the dataset changes, the source that the pivot table is looking
at is a dynamic range defined as :
=OFFSET(dataset!$A$1,0,0,COUNTA(dataset!$A:$A),COUNTA(dataset!$1:$1))
Because of that, I feel like it may be some sort of caching or memory
problem, but I just can't nail it down. I've tried this and that for clearing
cache, but to be honest, I don't know if it's working. It's not making the
sheet work faster.