Almost there... dynamic and conditional set of PivotItems

[s80NL]

Ok, I've got the following situation:

Sheets("Inhoud") or s1, the place where the default values are kept i
cells A110 (A = SheetName, B = PivotTablesName, C = PivotFieldsName
D = the default value)
Sheets("Contracten") or s2, with a PivotTable on it
Sheets("Afgelopen contracten") or s3, with a PivotTable on it
etc

I can set a default value to a PivotFields.PivotItems with this code:


Code
-------------------
' actually works with XP & Excel 2003
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields("AM")
Dim SPgField2 As Variant
s1.Range("D2").Select
SPgField2 = Selection
.CurrentPage = SPgField2
End Wit
-------------------


But then, it also sets the default value when the value wasn't presen
in the list before!!

I tried to make this piece of code conditional as you can see below...


Code
-------------------
' doesn't work... :S
Dim i As Long
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields("AM") 'Sheets("Aflopende contracten") = s3
For i = 1 To .PivotItems.Count
If .PivotItems(i) = s1.Range("D2").Select Then
Dim SPgField1 As Variant
s1.Range("D2").Select
SPgField1 = Selection
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
Else
MsgBox ("NO / i=" & i)
End If
Next i
End Wit
-------------------


But that isn't working, it somehow fails to recognize the value in th
if-statement, all I got was the NO message...

Anyone, who knows how to fix this?? I'm running XP with Excel 2003.

Ok, that was my first question. Now the second. How can I make the cod
dynamic so I can use it as a Function? Or how can I get the SheetsName
PivotTablesName and PivotFieldsName from s1 (Sheets("Inhoud")) column
A:C?

It would be awesome if we can get this working ;

 

[Die_Another_Day]

First one is easy
If .PivotItems(i) = s1.Range("D2").Select
should be
If .PivotItems(i) = s1.Range("D2").Value
Second one I'm not sure if I understand. You can always access stuff by
the index. ie. PivotTables(1)
post back with more info.

HTH

Die_Another_Day

 

[s80NL]

Absolutely awesome I've created to following code out of you
remarks, but I've to wait till tomorrow morning to test it at work..
so I'll let you know the results later.

How silly can it be Value instead of Select... Today, I've been starin
at that stupid line for ages


Code
-------------------
Sub ShouldWorkNow()
' NOT TESTED YET... will test it tomorrow at work
Dim i As Long
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields("AM")
For i = 1 To .PivotItems.Count
If .PivotItems(i) = s1.Range("D2").Value Then
Dim SPgField1 As Variant
s1.Range("D2").Select
SPgField1 = Selection
.CurrentPage = SPgField1
End If
Next i
End With
End Su
-------------------


The second part of my quest to make it dynamicly was to loop throug
all the values on Sheet("Inhoud") where I manage the default values.
think this should do the trick, but just as above, I haven't tested i
yet!


Code
-------------------
Function SetPivotFieldFunction(WS, PT, PF, OptionX) As Boolean
'NOT TESTED YET... will test tomorrow at work
Dim i As Long
With WS.PT.PF
For i = 1 To .PivotItems.Count
If .PivotItems(i) = OptionX Then
Dim SPgField1 As Variant
SPgField1 = OptionX
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
SetPivotFieldFunction True
Else
SetPivotFieldFunction False
End If
Next i
End With
End Functio
-------------------



Code
-------------------
Sub SetPivotField()
'NOT TESTED YET... will test tomorrow at work
Dim i As Long
Dim WS As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim OptionX As String

For i = 1 To 10
WS = s1.Range("A" & i)
PT = s1.Range("B" & i)
PF = s1.Range("C" & i)
OptionX = s1.Range("D" & i)

If SetPivotFfieldFunction(WS, PT, PF, OptionX) = True Then
MsgBox "True... " & PF & "." & OptionX, vbInformation
Else
MsgBox "False... " & PF & "." & OptionX, vbCritical
End If
Next i
End Su

 

[Die_Another_Day]

Dim i As Long
With WS.PT.PF
For i = 1 To .PivotItems.Count
If .PivotItems(i) = OptionX Then
Dim SPgField1 As Variant '!!!!!Move out of Loop!!!!!
SPgField1 = OptionX
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
SetPivotFieldFunction True
Else
SetPivotFieldFunction False
End If
Next i
End With
End Function

Dim statements should ALWAYS be at the begining of code.

Die_Another_Day