Pivot Table Columns force visible

[Guest]

Hi
I have a pivot table based upon a list I create via code, it collects job
codes for the week. Most weeks there is someone is working each day, but
sometimes if there is no one working on a Sunday, and as I don't want to a
section that shows who is not working, and no one is working on a Sunday
Excel does not bother to create a column for it. Which may be a reasonable
thing to do, but it moves my totals column 1 column to the left and that
gives me grief, because of my formatting.

Is there any way I can force it to give me 7 columns of data, even if a
column is empty?

I hope so!

 

[Guest]

Right click on the "Day_of_the_Week" Pivot field in the pivot table and
choose Field settings. In the lower left is a checkbox for show items with
no data. This assumes that Sunday has been used in the data before and
exists as an item for that field (even if there is no data there now).

 

[par_60056]

Hi
I have a pivot table based upon a list I create via code, it collects job
codes for the week. Most weeks there is someone is working each day, but
sometimes if there is no one working on a Sunday, and as I don't want to a
section that shows who is not working, and no one is working on a Sunday
Excel does not bother to create a column for it. Which may be a reasonable
thing to do, but it moves my totals column 1 column to the left and that
gives me grief, because of my formatting.

Is there any way I can force it to give me 7 columns of data, even if a
column is empty?

I hope so!

You can try right click the field and select field settings. Check
the box that says "Show Items with no Data"

Peter

 

[Guest]

Thanks Tom
That fixed it and showed up an error in my data collection macro!

And I recorded a macro doing it so now I can do it programatically as well!

 

[Guest]

I wrote too soon!
The error is not my data collection macro, but it appears to relate to,
where I have used other date ranges before, I have the line with
PivotCache.Refresh is there any way to clear it first? PivotCache.Clear
didn't work.
Or is there any other possibility?

 

[Guest]

A pivotcache gets deleted when you save a workbook and no pivottables refer
to that cache. There isn't a direct way way to totally clear and rebuild it.
If you are trying to eliminate some old data items that you don't want
displayed (which I think is what you really want) then you can use this:

Sub RemoveOldLabels()
Dim oPiv As PivotTable
Dim oField As PivotField
Dim oItem As PivotItem
Set oPiv = ActiveSheet.PivotTables(1)
For Each oField In oPiv.PivotFields
If oField.Name <> "Data" Then
For Each oItem In oField.PivotItems
If oItem.RecordCount = 0 And _
Not oItem.IsCalculated Then _
oItem.Delete
Next
End If
Next
End Sub

you might then need to add a Sunday data record to reinstate Sunday.

If you just want it to work on a specific pivotfield, modify the code to do
that.

 

[Guest]

Thanks Tom
I've stripped out the bits I need.
I just cycle through where oField.Name = "Date" and then compare oItem with
the range current starting point date to that +7, so I will get just the one
weeks data regardless of any calculation.