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
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