PivotTables Programming 1

[valdezdj]

I have a PivotTable that when Excel is opened it is updated for that day. I have warranties of all our computer and each type of warranty expires on different days. In my Data worksheet I've created Todays Date in a cell that automatically updates everyday. Basically what happens when the pivot table is updated it expands all the rows with various data, i.e., user and computer name but I don't want that. All I want is the days left and count of computers which is shown when the rows are collapsed. The reason for this, is because the PivotChart is distorted when these values are displayed. I know this sounds complicated but if you need clarification please ask. Thanks for the time and help on this.

 

[SkipVought]

Hi,

Welcome to Tek-Tips.

This does not appear to be a VBA question. It involves native Excel PivotTable functionality.

Please post in Microsoft: Office forum68.

Skip,

_[red][/red]

 

[valdezdj]

How is this not a VBA question? I have a VBA script Event that updates the pivot table on Open. Then I have recorded a macro to collapse the rows but that is static because it enters in the cell value for yesterday, I need it to be dynamic and enter in what the cell value will be today.

 

[SkipVought]

If you can do it in native Excel, it can be done in code. In fact, it can be macro recorded.

Have you done what you want to do in native Excel?

Skip,

_[red][/red]

 

[valdezdj]

No,
I wouldn't keep replying if I could do it in native Excel. If I were to post this thread in a different forum, I would probably be re-directed back here. For the recorded macro, I need to append the code so that it works everyday. I understand that if it can be recorded in a macro that it can be done in native excel, but the recording doesn't work everyday, I would have to go into the code and change the number for that day, which, doesn't make any sense to do a macro. That's what I need help here is the code:

Sub CollapseRows()
'
' CollapseRows Macro
'

'
Range("A4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Days until Expiration"). _
PivotItems("-54").ShowDetail = False
Range("A6").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Days until Expiration"). _
PivotItems("2").ShowDetail = False

This is the first two rows of the PivotTable so where it says "-54" I need it to be a cell instead of a value.I'm not sure if this is the right way or if I should just try a different script.

 

[SkipVought]

"...but the recording doesn't work everyday..."

Post the recorded code, PLEASE!

Skip,

_[red][/red]

 

[valdezdj]

That is the recorded code. I don't have anything else.

 

[pbrodsky]

The way I understand the original post, this does NOT need to involve VBA. If you could post what the original data looks like (actual sample values - not just a description) and what you want the pivot table to look like based on that data, then somebody could probably help a lot better.

 

[SkipVought]

"...so where it says "-54" I need it to be a cell instead of a value..."

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Days until Expiration"). _
        PivotItems(ActiveSheet.[A1].value).ShowDetail = False

or am I missing something

Skip,

_[red][/red]

 

[valdezdj]

Yes thank you that's all I needed, sorry for the confusion. I made harder than it really was. Thanks again for being patient and helping me.