PivotTable eliminating new pivot items

[clouddog9]

I have a macro that filters certain values from a pivottable, and this works fine, but when I insert new data (with another macro) the pivot table automatically eliminates the new pivot items. What would be the vba code to make the pivottable to accept the new pivot items but still keeping the pivot items that were perviously filtered out... out? Thanks in advance.

 

[SkipVought]

Hi,

Please be SPECIFIC.

Give concrete examples.

Post your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

There is no problem with the code. The code is working fine. My problem is with the mechanics of the pivot table. And this is what I am looking to alter with vba code.

An Example:

Let's say I have in one of my Page Fields Pivot Items "A", "B" and "C." The macro eliminates "C" based on user input. Tomorrow when I get more data, I see in the data that there will be a new pivot item ("D"). But when I input the data into the data tab and refresh the pivot table, pivot item "D" does not show up because I have a filter that only shows "A" and "B". I want the pivot table to show everything but "C."

Before you say make the code to just filter "C." The filtering can change daily. What I'd like to have happen is that the pivot table will show everything except what was previously eliminated.

Do I need to write code that will loop through the pivot items prior to the data upload to figure out their current visible status, then use code to show all, upload the data then loop through the visible status one more time to filter the previously filtered items? Or is there an easier way?

 

[PHV]

because I have a filter that only shows "A" and "B".
So, again, what is the code setting this filter ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

macro eliminates "C" based on user input.

What does THAT mean?

We need to see your code!!!

Is that so difficult?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

It isn't difficult, but since the code is working why post it? I really dont see how this is helpful, but here is the code where the items are filtered:

    For Each pi In pfCPFEC.PivotItems
        On Error Resume Next
        varMatch = WorksheetFunction.Match(pi.Name, aryPINames, 0) - 1
        If Err.Number <> 0 Then varMatch = -1
        On Error GoTo 0
        If varMatch <> -1 Then
            pi.Visible = aryPIStatus(varMatch)
        End If
        varMatch = 0
    Next

I'd post ALL the code, but trust me, you would not want that. You would be going through it for a while. There is almost 5800 lines of code (which all work just like there are supposed to).

As you will probably be able to tell, this segment of code above works exactley like it is supposed to.

Here is an example to stick in excel (I'd attach a spreadsheet, but my company has all internet storage sites blocked):

ID FirstName LastName Salary
A John Smith $50,000.00
B Freddy Doe $45,000.00
C Bruce Springsteen $25,000.00

Put this into a pivot table. Stick the IDs into the Page Field. Filter out "C." Now insert a row inbetween "B" and "C." Make the ID "D" and put what ever else in the other fields that you want. Refresh the pivot table. "D" does not appear. Hope this makes it clearer.

 

[PHV]

What about this ?
If Err.Number <> 0 Then varMatch = -1[!]: Err.Clear[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

So you have an array of values that you're comparing the PIs to. Where did THAT come from? Help yourself out, man!!! Do you want to make this a 20 questions deal?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

So you have an array of values that you're comparing the PIs to. Where did THAT come from? Help yourself out, man!!! Do you want to make this a 20 questions deal?"

Voight:

First, I know I am asking for help, but I am getting a little tired of your sarcasm. We are all professionals here, so please let's treat each other as such.

Second, there is sample data for somebody to use to make a simple pivot table to replicate the issue I am having. Please read the entire thing, not just whatever you think the highlights are, and try the example. If the example does not make sense, PLEASE TELL ME what doesn't make sense, so I can clarify if need be.

Third, the reason I didn't want to put the code up was because THERE IS NO PROBLEM WITH THE CODE! I get no error messages. THE CODE PREFORMS JUST LIKE IT SHOULD. The problem lies in the pivot table itself. The sample code was for PHV who asked for the code where the filter was setup.

PHV:

Thanks, but this isn't the problem I am having.

 

[SkipVought]

The problem lies is the fact that you have identified certain PIs to view, using the PAGE field area and adding PIs does not change that criteria. The criteria is not base on 'show me everything except C.' Rather it is base on the PIs you explicitly selected. That's how the filter works in Excel 2007 Pivot Tables.

So if you want to do that, you need code that 1) defines the criteria, 2) assigns the criteria and 3) remembers the criteria when PIs are added, in order to assign the criteria in the manner you wish.

THAT IS ALL CODE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Something like this might work for you, given 1) a Source Table for the PT named MyTable, a table of excluded values named ExlcudeList

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oPI As PivotItem, r As Range
    If Not Intersect(Target, [MyTable].CurrentRegion) Is Nothing Then
        For Each oPI In ActiveSheet.PivotTables(1).PivotFields("ID").PivotItems
            For Each r In [ExcludeList]
                If r.Value <> oPI.Value Then oPI.Visible = True
            Next
        Next
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

So if you want to do that, you need code that 1) defines the criteria, 2) assigns the criteria and 3) remembers the criteria when PIs are added, in order to assign the criteria in the manner you wish."

This is exactley what I needed to know. I was thinking that this was an option, but I was hoping for an easier solution (and potientially less time consuming). Apparently there is not one (unless somebody else has another suggestion). Thanks.

 

[SkipVought]

The code posted above is not suitable?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

I am not sure yet. I have not go to try it (busy with some month end reports for work that I was told took priority). But I am going to try something similar. I will post my code when I get it working to see if it is similar and for you to see if it is another valid way to do it. Thanks again.