kitsu_ne
New member
- Joined
- Jan 15, 2014
- Messages
- 16
- Reaction score
- 0
- Points
- 0
I've nearly finished a button that modifies the values of a pivot table. I had it working at one point, and now I don't know what I've messed up.
The statement "Set pf = pt.PivotFields("JI")" does not seem to be working correctly, even though it once would. When I execute the button, the function runs nearly perfectly, but stops at the end when it references pf.PivotItems
By this I mean, when it gets to the line "For Each pi In pf.PivotItems" It displays the error "Run-Time Error '91': Object Variable or Withblock variable not set"
Can you see why "Set pf = pt.PivotFields("JI")" isn't working? Is it because I have the "JI" field as a Filter, rather than a row/column field??? When you hover over it it shows "pf = "Nothing".
Also, JI is set to allow multiple items to be filtered / displayed.
Thanks...
~Kitsu
The statement "Set pf = pt.PivotFields("JI")" does not seem to be working correctly, even though it once would. When I execute the button, the function runs nearly perfectly, but stops at the end when it references pf.PivotItems
By this I mean, when it gets to the line "For Each pi In pf.PivotItems" It displays the error "Run-Time Error '91': Object Variable or Withblock variable not set"
Can you see why "Set pf = pt.PivotFields("JI")" isn't working? Is it because I have the "JI" field as a Filter, rather than a row/column field??? When you hover over it it shows "pf = "Nothing".
Also, JI is set to allow multiple items to be filtered / displayed.
Thanks...
Code:
Sub MDC_WTEST()
MDC_TableUpdate "A", "G", "P" 'These are the values we want to display, they are used later in the function.
End Sub
Code:
Function MDC_TableUpdate(val1 As String, Optional val2 As String, Optional val3 As String)
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("MDC_Table")
Dim pf As PivotField
Set pf = pt.PivotFields("JI")
Dim pfDate As PivotField
Set pfDate = pt.PivotFields("CREATE")
Dim stDate As Date
stDate = MDC_GetDate("Start")
Dim edDate As Date
edDate = MDC_GetDate("End")
'Before we can update the pivot table we will need to reset it (clear the filters).
' We will also need to set it up so that "WES" jobs do not dispay.
pt.ClearAllFilters
pt.PivotFields("WES").CurrentPage = "(blank)"
' CurrentPage tells the pivot table to filter the WES item by one value,
' "(blank)", which is the main job we want to look at. If there is something else we don't want it.
'This next bit checks to ensure all pivot fields are visible. If they are not, it fixes that.
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.Visible = False Then
pi.Visible = True
End If
Next pi
Next pf
'Now we make all dates that do not fit the date range become invisible.
For Each pi In pfDate.PivotItems
'goes through every PivotItem in the PivotField
If Not pi.Name = "(blank)" Then
'ignores blanks and then checks if date is in range
If pi.Visible = True Then
If pi.Value < stDate Then pi.Visible = False
If pi.Value > edDate Then pi.Visible = False
End If
End If
Next pi
'We do the same for the value(s) we want to display.
For Each pi In pf.PivotItems 'This is where the code gets highlighted as an error.
If pi.Value <> val1 Or pi.Value <> val2 Or pi.Value <> val3 Then pi.Visible = False
Next pi
End Function
~Kitsu
Last edited: