Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default Code to filter a pivot table

Hi,

I've got a workbook that is connected to an external dataset. The
workbook is just a pivot table that pulls data from the external
dataset. It shows sales counts for a specified product for all
countries worldwide for all or selected years. The pivot table shows
all countries vertically in column B, and the years horizontally in
row 4, and of course with the data counts in the adjacent cells.

Users of the pivot table regularly need to filter the countries down
to a specific subset of 33 countries. Not all of those 33 countries
are in the returned data, but they have to look through the entire
list of ALL countries returned and manually select those in the subset
that are there. This is obviously tedious and error prone.

I've tried to adapt a VBA solution I found to filter the list, but I
keep getting an error message saying 'Unable to get the PivotFields
property of the PivotTable class'. I'm wondering if that's because
the pivot table is pulling from an external dataset connection. That
code is at the bottom.

I also tried a Slicer with a VBA solution to automatically filter the
Slicer. Below is a small part of the code for applying that subset of
countries filter. It works, so long as the country is actually in the
data the pivot table returns for the given product that is being
looked at. If the product isn't sold in that country, say Belgium for
instance, the macro bugs out...because it can't filter on something
that isn't there.

Any thoughts on what might be wrong with the filter code at the
bottom, or alternately, how I might be able to adjust the Slicer code
to bypass an item in the array if it's not present in the pivot table
country list?

Thanks!!

Slicer Code:
Sub SetEUcountries()
.VisibleSlicerItemsList = Array( _
"[Financial Org].[Top Countries].[Country Name].&[Other
OUS]&[AUSTRIA]", _
"[Financial Org].[Top Countries].[Country Name].&[Other
OUS]&[Belgium]", _
"[Financial Org].[Top Countries].[Country Name].&[OUS]&[UNITED
KINGDOM]")
End Sub

Pivot table filter code:
Private Function Filter_PivotField(pvtField As PivotField, _
varItemList As Variant)
Dim strItem1 As String
Dim i As Long
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False

strItem1 = varItemList(LBound(varItemList))
With pvtField
.PivotItems(strItem1).Visible = True
For i = 1 To .PivotItems.Count
If .PivotItems(i) < strItem1 And _
.PivotItems(i).Visible = True Then
.PivotItems(i).Visible = False
End If
Next i
For i = LBound(varItemList) + 1 To UBound(varItemList)
.PivotItems(varItemList(i)).Visible = True
Next i
End With
Exit Function
ErrorHandler:
MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Sub Filter_ItemListInRange()
Filter_PivotField _
pvtField:=Sheets("Sales
Figs").PivotTables("PivotTable1").PivotFields("Cou ntry"), _
varItemList:=Application.Transpose(Sheets("EU Countries Unit
Sold In").Range("EUcountries"))

End Sub

Sub test()
Dim PI As PivotItem
With Worksheets("Sales
Figs").PivotTables("PivotTable1").PivotFields("Cou ntry")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible =
WorksheetFunction.CountIf(Range("EUcountries"), PI.Name) 0
Next PI
End With
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot table - Filter - no filter by value Andrew in Jax Excel Discussion (Misc queries) 0 September 18th 09 02:38 PM
Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table's Source Range) Mike C[_5_] Excel Programming 3 February 15th 08 07:22 AM
Filter lines with Pivot table and non pivot table columns Grover Charts and Charting in Excel 4 September 28th 07 03:16 AM
Filter lines with Pivot table and non Pivot table columns Grover Excel Discussion (Misc queries) 1 September 26th 07 12:48 AM
Filter lines containing pivot table and non pivot table data Grover Excel Worksheet Functions 0 September 24th 07 07:20 PM


All times are GMT +1. The time now is 11:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"