PDA

View Full Version : Solved: Pivotable.pivotfield.pivotitem problem



artds
11-27-2007, 11:04 PM
Hi all!
yup, its me again, still workin an ongoing project, which I have posted before. I am testing this set of codes which I got from http://www.contextures.com/xlPivot03.html , from a 'Jeff bloomer'. After modifying it slightly to fit my requirement, here is how it looks.


Sub Populate_OLTbls()
Dim TblNm As Range, n As Name
Dim txt As String
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Sheet5.Activate
Set ws = ActiveSheet
Set pt = Sheet1.PivotTables("PivotTable2")
Set pf = pt.PivotFields("Distributor")

For Each n In ws.Names
Set TblNm = Range(n)
txt = TblNm.Cells(1, 1).Text
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
pi.Visible = False
Next pi
.PivotItems(txt).Visible = True
.AutoSort xlAscending, .SourceName
End With
Next n

End Sub


My intention is to hide all the pivot items in this "pivotTable2" except one, which has the same name as the current table in sheet5. That is, 5 pivot item=5 tables(or name ranges) in sheet 5. Each table has the same name in its cells(1,1) with the pivot items.

The codes works fine, in fact I think its brilliant, as I probably won't be able to come up with this on my own. However, when " pi.Visible = False" has reached the last visible pivot item in the pivot table, it prompt me an error:
"run-time error '1004'"
"Unable to set the Visible property of PivotItem class"

I'm comtemplating in putting an error handler, "On Error Resume Next", however, in the end result, 2 pivot items will be visible. When all I want is just 1. Anyone has any idea as how to proced? Any advice will be greatly appreciated. Thank you in advance for any help rendered.

Maybe, once I am 90% complete, I will post the file here for reviews and feedback, and as a good/bad example to others. What say you, gurus, expert, moderators, regulars?

regards,
artds

Charlize
11-28-2007, 01:24 AM
Maybe because you can't hide all the pivotitems. If I understood your coding correctly, you better check if the pivotitems(txt) has the same value as pi. Not tested because I don't have a sample workbookSub Populate_OLTbls()
Dim TblNm As Range, n As Name
Dim txt As String
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Sheet5.Activate
Set ws = ActiveSheet
Set pt = Sheet1.PivotTables("PivotTable2")
Set pf = pt.PivotFields("Distributor")

For Each n In ws.Names
Set TblNm = Range(n)
txt = TblNm.Cells(1, 1).Text
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
'pi.value or pi.name or ... don't know which
'to choose from
If pi.Value <> .PivotItems(txt) Then
pi.Visible = False
End If
Next pi
'.PivotItems(txt).Visible = True
.AutoSort xlAscending, .SourceName
End With
Next n
End Sub

artds
11-28-2007, 02:17 AM
Hi charlize! I understand what you mean. After I posted this thread, I did add the conditional if statement, similar to yours. Even to the extend of adding another "For..Each" Statements, just to make everthing visible again. Still the error comes.




For Each n In ws.Names
Set TblNm = Range(n)
'cells(1,1) contain the name of the distributor in the table
txt = TblNm.Cells(1, 1).Text
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If Not pi = txt Then
pi.Visible = False
End If
Next pi
.AutoSort xlAscending, .SourceName
Call MsgBox("Visible Pivot Item: " & txt, vbInformation, Application.Name)
Sheet1.Activate
Range("C6:P7").Select
Selection.Copy
Application.Goto Reference:="'Ord Load - Total'!Table" & cnt
ActiveCell.Cells(4, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheet1.Activate
For Each pi In pf.PivotItems
If Not pi.Visible = True Then
pi.Visible = True
End If
Next pi
End With
Next n

Charlize
11-28-2007, 03:01 AM
Better post a sample workbook so we can try something out. And what's the meaning of For Each n In ws.Names?

artds
11-28-2007, 10:38 AM
Hi Charlize,

I already uploaded a sample file. Hope it helps. To answer your question,
Here are the variables which i decalre:


Dim ws As Worksheet
Dim TblNm As Range, n As Name
Dim txt As String, cnt As Long
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

thus, "For each n in ws.Names".....
in sheet 5, there are 5 named tables.

Hope this helps. Appreciate all your help. Thanks

regards,
artds

artds
11-29-2007, 07:19 PM
Hi all! Apparently, I was able to solve the problem. But I'm not too sure if this solution is the best. But since it works for me, its great.

What was needed to be done was to make all the pivot item visible again. I haven't add the application.screenupdating line yet. That will be added on later. So what was needed is me repeating these sets of codes(underlined codes). Any thoughts anyone?



Dim ws As Worksheet
Dim TblNm As Range, n As Name, txt as String
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

For Each n In ws.Names
Set TblNm = Range(n)
txt = TblNm.Cells(1, 1).Text
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If Not pi = txt Then
pi.Visible = False
End If
Next pi
.AutoSort xlAscending, .SourceName
Call MsgBox("Visible Pivot Item: " & txt, vbInformation, Application.Name)

'calling other procedures
Expand_tbls TblNm
Populate_tbls TblNm

.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
If Not pi.Visible = True Then
pi.Visible = True
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
Next n


Thanks anyway, for your time and effort charlize. Really appreciate it.:hi:
regards,
artds

dionjuan2004
01-21-2008, 06:05 AM
I have a similar problem. I'm working with a Pivot based on an OLAP Cube. I have tried the above mentioned solution but it does not work. I tried to somehow use "HiddenItemsList" property as well but to no avail. I just need to be able to make one Pivot Item visible at a time and keep all the others hidden and then do some basic charting.

Any help will be much appreciated.
Dion

artds
01-21-2008, 06:28 PM
hey dionjuan, have you take a look at my first posting? Where i added the URL? Thats where I got codes from. PLus, maybe you should show us your codes so as to help us give a better picture.

regards
artds

dionjuan2004
01-21-2008, 09:01 PM
Thanks for your reply, here is my code. And I have throughly read the link posted by you, but still could not get this working. I think that because it is the OLAP based pivot, that is what is creating troubles.

For example, this piece of code
pvtField(FieldName).PivotItems.count This only counts the visible number of Pivot Items and not all of them, which is very different from Pivot Tables with source other than OLAP Cube.

Anyways, this is my Code -


strSortField = pvtField.AutoSortField
lngSortOrder = pvtField.AutoSortOrder

For Each rCell In Range(myNameRange)

pvtTable.ManualUpdate = True
pvtField.AutoSort xlManual, strSortField

' Make every item invisible except the current one

For Each pvtItem In pvtField.PivotItems
If pvtItem.Caption <> rCell.value Then
pvtItem.Visible = False
End If
Next pvtItem

' Here do stuff

' Now make every item visible

For Each pvtItem In pvtField.PivotItems
pvtItem.Visible = True
Next pvtItem

pvtTable.ManualUpdate = False
pvtField.AutoSort lngSortOrder, strSortField

Next