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 wrote:
Ok, I've got the following situation:
Sheets("Inhoud") or s1, the place where the default values are kept in
cells A1:D10 (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 With
--------------------
But then, it also sets the default value when the value wasn't present
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 With
--------------------
But that isn't working, it somehow fails to recognize the value in the
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 code
dynamic so I can use it as a Function? Or how can I get the SheetsName,
PivotTablesName and PivotFieldsName from s1 (Sheets("Inhoud")) columns
A:C?
It would be awesome if we can get this working ;)
--
s80NL
------------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
View this thread: http://www.excelforum.com/showthread...hreadid=561968