I have a worksheet named range that changes. On another worksheet (i
the same workbook) I have a Pivot Table that uses the named range a
the data source. And as we all know, Pivot tables don't refres
themselves, I have a macro that does just that - refresh the pivo
table whenever the named range changes. Anyway, I also want to writ
the data from the pivot table onto a another worksheet for whisch
have subroutine (see code below). Unfortunately, when the pivot tabl
refreshes, the combo box for the Row data item retains the old items
Ofcourse these items have no corresponding column data value. So th
problem at hand, is that when I scroll through the code to write th
data from the Pivot Table to the other worksheet, it writes th
invisible data row items - which of course I don't want. I just wan
the current visible items.


Code
-------------------

Public Sub Write_PivotData2()

Dim pvtTable As PivotTable
Dim pvtItem As PivotItem

Set nwSheet = Worksheets("MDTRPT")
Set pvtTable = Worksheets("PIR-DT DESC").PivotTables("PIR1DTDESC")

rw = 12
For Each pvtItem In pvtTable.PivotFields("DTDESC").PivotItems
rw = rw + 1
nwSheet.Cells(rw, 2).Value = pvtItem.Name
Next pvtItem

End Sub


-------------------


I even tried using VisibleItems instead of PivotItems and that didn'
work either
I even tried to circumvent writing the "invalid" data row items b
using "On Error Resume Next", but this wouldn't work because I stil
can't tell the difference what are the current visible data row item
and what are the old one. Because the column data value does no
exist, I get an error trying to determine if it is NULL (see cod
below) - "The formula is not complete. Make sure an ending squar
bracket ] is not missing"



Code
-------------------

Public Sub Write_PivotData()

Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim pvtSumValue As Double
Dim pvtItemValue As String

Set nwSheet = Worksheets("MDTRPT")
Set pvtTable = Worksheets("PIR-DT DESC").PivotTables("PIR1DTDESC")

rw = 12

For Each pvtItem In pvtTable.PivotFields("DTDESC").VisibleItems
pvtItemValue = pvtItem.Name
pvtSumValue = pvtTable.GetData(pvtItemValue) 'Error occurs on this line
If IsNull(pvtSumValue) Then
'It is a Pivot field from before
Else
rw = rw + 1
nwSheet.Cells(rw, 2).Value = pvtItem.Name
End If
Next pvtItem

End Sub

-------------------

--
suzette
-----------------------------------------------------------------------
suzetter's Profile: http://www.excelforum.com/member.php...nfo&userid=707
View this thread: http://www.excelforum.com/showthread.php?threadid=39022

There's information here on clearing old items in a pivot table:


http://www.contextures.com/xlPivot04.html


suzetter wrote:
I have a worksheet named range that changes. On another worksheet (in
the same workbook) I have a Pivot Table that uses the named range as
the data source. And as we all know, Pivot tables don't refresh
themselves, I have a macro that does just that - refresh the pivot
table whenever the named range changes. Anyway, I also want to write
the data from the pivot table onto a another worksheet for whisch I
have subroutine (see code below). Unfortunately, when the pivot table
refreshes, the combo box for the Row data item retains the old items.
Ofcourse these items have no corresponding column data value. So the
problem at hand, is that when I scroll through the code to write the
data from the Pivot Table to the other worksheet, it writes the
invisible data row items - which of course I don't want. I just want
the current visible items.


Code:
--------------------

Public Sub Write_PivotData2()

Dim pvtTable As PivotTable
Dim pvtItem As PivotItem

Set nwSheet = Worksheets("MDTRPT")
Set pvtTable = Worksheets("PIR-DT DESC").PivotTables("PIR1DTDESC")

rw = 12
For Each pvtItem In pvtTable.PivotFields("DTDESC").PivotItems
rw = rw + 1
nwSheet.Cells(rw, 2).Value = pvtItem.Name
Next pvtItem

End Sub


--------------------


I even tried using VisibleItems instead of PivotItems and that didn't
work either
I even tried to circumvent writing the "invalid" data row items by
using "On Error Resume Next", but this wouldn't work because I still
can't tell the difference what are the current visible data row items
and what are the old one. Because the column data value does not
exist, I get an error trying to determine if it is NULL (see code
below) - "The formula is not complete. Make sure an ending square
bracket ] is not missing"



Code:
--------------------

Public Sub Write_PivotData()

Dim pvtTable As PivotTable
Dim pvtItem As PivotItem
Dim pvtSumValue As Double
Dim pvtItemValue As String

Set nwSheet = Worksheets("MDTRPT")
Set pvtTable = Worksheets("PIR-DT DESC").PivotTables("PIR1DTDESC")

rw = 12

For Each pvtItem In pvtTable.PivotFields("DTDESC").VisibleItems
pvtItemValue = pvtItem.Name
pvtSumValue = pvtTable.GetData(pvtItemValue) 'Error occurs on this line
If IsNull(pvtSumValue) Then
'It is a Pivot field from before
Else
rw = rw + 1
nwSheet.Cells(rw, 2).Value = pvtItem.Name
End If
Next pvtItem

End Sub

--------------------




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html