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),COU NTA(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.

Holy macrel! Months! I have revisited this every month since February and
finally found an answer!

I modified the line
For Each pi In .PivotItems
to say
For Each pi In pf.PivotItems

Had to declare it at the top as
Dim pf As PivotField

and now it's instant. I think it was checking every cell value, not just
pivot field values, or something. I'm a gross beginner at VBA, so I'm not
totally sure what it was doing, but it occurred to me that it was going
through too many records, because when I interrupted the process it was
always stuck in the same place (at the end of that particular if statement)
so I looked at it closer, poked around and tried this...

"Justin Larson" wrote:

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),COU NTA(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.

Holy macrel! Months! I have revisited this every month since February and
finally found an answer!

I modified the line
For Each pi In .PivotItems
to say
For Each pi In pf.PivotItems

Had to declare it at the top as
Dim pf As PivotField

and now it's instant. I think it was checking every cell value, not just
pivot field values, or something. I'm a gross beginner at VBA, so I'm not
totally sure what it was doing, but it occurred to me that it was going
through too many records, because when I interrupted the process it was
always stuck in the same place (at the end of that particular if statement)
so I looked at it closer, poked around and tried this...

"Justin Larson" wrote:

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),COU NTA(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.

well done & thank you for sharing this with us all.

best wishes
Patrick

"Justin Larson" wrote in message
...
Holy macrel! Months! I have revisited this every month since February and
finally found an answer!

I modified the line
For Each pi In .PivotItems
to say
For Each pi In pf.PivotItems

Had to declare it at the top as
Dim pf As PivotField

and now it's instant. I think it was checking every cell value, not just
pivot field values, or something. I'm a gross beginner at VBA, so I'm not
totally sure what it was doing, but it occurred to me that it was going
through too many records, because when I interrupted the process it was
always stuck in the same place (at the end of that particular if
statement)
so I looked at it closer, poked around and tried this...

"Justin Larson" wrote:

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),COU NTA(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.