J
Justin Larson
I'm having a strange problem, and am hoping someone here can enlighten me a
bit. As pretext, I have NO background in VBA, so any advice must come in the
form of a-monkey-could-be-taught language.
I have a workbook with several sheets. On each of these sheets is one or
more Pivot tables. I have setup VBA code so that when a set of control cells
is updated, all page fields in all of the pivot tables changes to the new
value from the control cell. So the user chooses a date and a state from a
menu, and all the various reports throughout the workbook should show data
for that state/date.
For the purposes of testing I have set up the control cells to be B1 and C1.
After I get the code working, I will change them to something hidden and use
form controls to update the cells (they're prettier).
The code reads that if the value entered can't be found, it defaults to
(All) which exists in every pivot table.
In any case, I have gotten to the point that when you change either item, it
updates both page fields on every pivot table in the workbook, which is what
it should do. The problem is that it can never find the date value for the
first pivot table. No matter what date value I put into the control cell,
that first table goes to (All). The rest of them are updating correctly. All
the pivot tables are drawing from the same source.
Before you ask, this code is primarily borrowed from someone else's, I did
not write it from scatch, just cut/paste/modified to meet needs.
Here's the code, any ideas, you VBA geniuses?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "State"
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"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
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
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
-Liver
bit. As pretext, I have NO background in VBA, so any advice must come in the
form of a-monkey-could-be-taught language.
I have a workbook with several sheets. On each of these sheets is one or
more Pivot tables. I have setup VBA code so that when a set of control cells
is updated, all page fields in all of the pivot tables changes to the new
value from the control cell. So the user chooses a date and a state from a
menu, and all the various reports throughout the workbook should show data
for that state/date.
For the purposes of testing I have set up the control cells to be B1 and C1.
After I get the code working, I will change them to something hidden and use
form controls to update the cells (they're prettier).
The code reads that if the value entered can't be found, it defaults to
(All) which exists in every pivot table.
In any case, I have gotten to the point that when you change either item, it
updates both page fields on every pivot table in the workbook, which is what
it should do. The problem is that it can never find the date value for the
first pivot table. No matter what date value I put into the control cell,
that first table goes to (All). The rest of them are updating correctly. All
the pivot tables are drawing from the same source.
Before you ask, this code is primarily borrowed from someone else's, I did
not write it from scatch, just cut/paste/modified to meet needs.
Here's the code, any ideas, you VBA geniuses?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "State"
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"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
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
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
-Liver