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