Missing Pivot Data in Pivot Field (PF)-Weird!

[Qull666]

Missing Pivot Data in Pivot Page Field (PF)-Weird!

Dear All,

Does anyone have an idea of why this happens. It's not often but sometimes.

This will be the first time after almost a year now. (had it like less than
5 times over 5 year)

My database is intact with every column and row filled with data.

No. of Rows: 2,400++
No. of Columns: 60++

This is what is missing:

Pivot Page Field is a list of bill no. from the database Column A (the most
primary column)


Pivot Page Field - Inv-No:
a) SM-06-30983
b) SM-06-30984 <----- this is missing (not even in the list), WEIRD
c) SM-06-30985

The other Pivot Tables are working except for this one particular Pivot Table.


So I Troubleshoot---> Database (but didn't work)

1. I have gone through the database structure
2. Re-typed the whole thing.
3. Copy > Paste Special > Value
4. Used Macro (Debra D's Change Pivot Field to force it out)
5. Used SM-06-30983 information and change to SM-06-30984
6. Change 1 of the 6 SM-06-30985 data line to SM-06-30984
7. Refresh like more than a million times.
8. Searched the advance list (hide list)
9. Look at the forum for a solution.


I just don't know if I wanna cry or laugh!!!


The only way I know is to rebuilt the Pivot Table AGAIN!!!

I was thinking of not to coz this pivot table is an invoice document with
lots of stuff. (rebuild is the last option)


If anyone has an idea and experienced of how to trouble shoot and retrieve
the data, that is a great cause.

Thanks all!!!
I am on the XLS 2000.

 

[Debra Dalgleish]

Is the invoice number in the source data, in the range that's used for
the pivot table? Maybe it's in the last few rows, and isn't being included?

 

[Qull666]

Dear Debra, Thanks for replying.

Even if I change the 1st row (A2) to that Invoice number, it would not
appear (tried that). Its one of those days when it happens. lol.

The range is OK coz I use the Dynamic Data Range. (thanks to you)
(before this I would range the whole column, 60 columns and 1mb bcomes 20mb
for just one Pivot Table)

The invoice number is the 1st column, that makes it the most primary column.

And I used this VBA, hoping to flush it out.: (got this through one of your
helps!)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = Worksheets("VR-9-1-Invoice Report").PivotTables(1)
Set pf = pt.PageFields("Inv-No")

If Target.Address = "$E$1" Then

Application.EnableEvents = False
For Each pi In pf.PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
pf.CurrentPage = pi.Value
Exit For
End If
Next pi
Application.EnableEvents = True
End If
End Sub

The other 2 would appear.

It would not be fair to get you to trouble shoot this, but if you ever found
the solution, maybe put it on the Contexture website. I visit the website
quite often.

Thank you.

 

[Qull666]

Dear Debra D,

If you would like a copy of the file with this weird thing, just send me an
email to (e-mail address removed). Thank you.

 

[Debra Dalgleish]

The caption for that item has been overwritten somehow. It appears as an
empty label at the bottom of the list of items. You could use a macro to
reset the captions for the page fields:

'=======================
Sub ResetPageCaptions()
'retrieve original field names
'if captions have been typed into pt
Dim pt As PivotTable
Dim pi As PivotItem
On Error GoTo errHandler

Application.ScreenUpdating = False
For Each pt In ActiveSheet.PivotTables
pt.ManualUpdate = True

For Each pi In pt.PageFields(1).PivotItems
pi.Caption = pi.SourceName
Next pi

pt.RefreshTable
pt.ManualUpdate = False
Next pt

exitHandler:
Set pi = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler

End Sub

'=============================

 

[Qull666]

Dear Debra,

It works. Thank you so much.

The caption for that item has been overwritten somehow. It appears as an
empty label at the bottom of the list of items. You could use a macro to
reset the captions for the page fields:

'=======================
Sub ResetPageCaptions()
'retrieve original field names
'if captions have been typed into pt
Dim pt As PivotTable
Dim pi As PivotItem
On Error GoTo errHandler

Application.ScreenUpdating = False
For Each pt In ActiveSheet.PivotTables
pt.ManualUpdate = True

For Each pi In pt.PageFields(1).PivotItems
pi.Caption = pi.SourceName
Next pi

pt.RefreshTable
pt.ManualUpdate = False
Next pt

exitHandler:
Set pi = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler

End Sub

'=============================