Pivot Table Page Fields Selection

[Mouimet]

Hi,
IN VBA how can I select only the data I need in the Pivot Table Page section.
Need to get only the data <45 in column "Days"
"Days" is in the Page Section of the pivottable.

The user will only click a button to answer different questions, and
generate a new data sheets base on is criterias and then, the pivot table is
refresh. the user will not select any other data.Most of the macro is ok. My
problem is only selecting the <45.
On the Page fields the pivot table should always select "DAYS" <45. from the
data.
I tried different macro and I can't find the solution.
I found something usefull on the newsgroups however when the macro do not
see any data smaller than 45 it stop with an error message.
Please help. Thanks

 

[james]

might be hard to decipher, but generally:

1. set cubefield to enable multiple page items
2. decide whether the pivot is 2003 or 2007 version (not application
version, pivot version)
3. depending on version create array and set to visbile page items
(2007) or iterate through items and add (2003)

hope it helps.


Private Sub SetCurrency_Pivot(ByRef pvtTable As PivotTable, ByVal
strCurrency As String, ByVal enmPivotVersion As EPivotVersion)

On Error GoTo ErrorTrap

Dim pvfCurrent As Object

With pvtTable

For Each pvfCurrent In .PageFields

If pvfCurrent.Name = "[Report Currency].[Report Currency].
[Report Currency]" Or pvfCurrent.Name = "[Report Currency].[Report
Currency]" Then

.ManualUpdate = True
If .CubeFields("[Report Currency].[Report
Currency]").EnableMultiplePageItems = False Then
.CubeFields("[Report Currency].[Report
Currency]").EnableMultiplePageItems = True
End If

If enmPivotVersion = EPivotVersion.Pivot2007 Then

.ManualUpdate = True
pvfCurrent.VisibleItemsList = Array("")

If strCurrency <> "NULL" Then
.ManualUpdate = True
pvfCurrent.VisibleItemsList = Split
(ReturnMultiValueString("[Report Currency].[Report Currency].&[",
strCurrency), ",")
End If

ElseIf enmPivotVersion = EPivotVersion.Pivot2003 Then

If strCurrency <> "NULL" Then
.ManualUpdate = True
SetPageFields_MultiValue pvtTable, "[Report
Currency].[Report Currency]", "[Report Currency].[Report Currency]",
strCurrency
End If

End If

End If

Next pvfCurrent

End With

ExitSub:

If IsObject(pvfCurrent) Then Set pvfCurrent = Nothing

Exit Sub

ErrorTrap:

GoTo ExitSub

End Sub


Private Function ReturnMultiValueString(ByVal strPrefix As String,
ByVal strValues As String) As String

On Error GoTo ErrorTrap

Dim varStrings As Variant
Dim intArrayCounter As Integer
Dim strReturnString As String

varStrings = Split(strValues, ",")

For intArrayCounter = 0 To UBound(varStrings)

strReturnString = strReturnString & Trim(strPrefix) & Trim
(varStrings(intArrayCounter)) & "],"

Next intArrayCounter

ReturnMultiValueString = Left(strReturnString, Len
(strReturnString) - 1)

ExitFX:

Exit Function

ErrorTrap:

GoTo ExitFX

End Function

Private Function SetPageFields_MultiValue(ByRef pvtTable As
PivotTable, ByVal strPageFieldTop As String, ByVal strPageFieldDetail
As String, ByVal strValues As String) As Boolean

On Error GoTo ErrorTrap

Dim varStrings As Variant
Dim intArrayCounter As Integer

With pvtTable

varStrings = Split(strValues, ",")

For intArrayCounter = 0 To UBound(varStrings)

.ManualUpdate = True
If intArrayCounter = 0 Then

.PivotFields(strPageFieldTop).AddPageItem
strPageFieldDetail & ".&[" & varStrings(intArrayCounter) & "]", True


Else

.PivotFields(strPageFieldTop).AddPageItem
strPageFieldDetail & ".&[" & varStrings(intArrayCounter) & "]"

End If

Next intArrayCounter

End With

SetPageFields_MultiValue = True

ExitFX:

Exit Function

ErrorTrap:

GoTo ExitFX

End Function