One More Pivot Table Question 1

[bernie10]

One more pivot table question, if yall don't mind.

Is there an easy way to "show all" on the table? In other words I have a table with like 20 fields and I'll commonly set filters for say 7 different fields. Sometimes we'll forget which fields were filtered, and when we want to see all the data, we will forget to "Show All" on all the fields. It would be convienent to be able to somehow reset all the filters, where all the data is shown with one click.

Any thoughts?

Thanks,
Collen

 

[SkipVought]

Hi,

Questions of this nature are usually best suited to the Visual Bacsi for Applications Forum707 since it is a programatic solution.

Sub ResetAllPivotItems()
    Dim pvt As PivotTable, pvf As PivotField, pvi As PivotItem
    For Each pvt In ActiveSheet.PivotTables
        For Each pvf In pvt.PivotFields
            For Each pvi In pvf.PivotItems
                pvi.Visible = True
            Next
            
        Next
    Next
End Sub

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

Thanks for your help, Skip. I wasn't sure if perhaps there was a way to do it without code so that's why I posted here. Point taken though.

Thanks again for the code,
Collen

 

[bernie10]

Looks like I spoke too fast... the code worked on the first test spreadsheet I tried, but then failed on the actual production sheet. I get an error "unable to set the visible property of the "pivotitem class"

Any thoughts?

Thanks again,
Collen

 

[SkipVought]

Please describe where the source data is, where the pivot table is, where the code is, and what is the state of the workbook when you run the code (what sheet/reference is active/selected) and how you actually run the code.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[SkipVought]

also describe what fields are PAGE, ROW, COLUMN, DATA

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

Okay,

The source data is on a different sheet but in the same workbook as the pivot table. The code is stored as a macro in the workbook. I am running the macro from the Visual Basic window by clicking "Run". The active sheet is the one with the pivot table on it when I run the code.

The Page field is a single text field. There are 19 row fields, which are either text or date format. There are 7 data fields which are numeric.

If I step through the code in the debugger it errors out the first time it tries to set the .visible property to true. If I change the code to say skip the first pivot value and set the property on the second or third it gives the same error. It also gives an error whether the particular pivot value is already visible or not.

Please let me know if there is any other info that might be helpful. I really appreciate the help.

Collen

 

[SkipVought]

Frankly,I'm fishing

    Dim pvt As PivotTable, pvf As PivotField, pvi As PivotItem
    For Each pvt In ActiveSheet.PivotTables
        For Each pvf In pvt.PivotFields
            For Each pvi In pvf.HiddenItems
                pvi.Visible = True
            Next
        Next
    Next

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[SkipVought]

I M curious.

How did you get my code into your module?

Copy 'n' paste or type?

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

Skip,
No luck...

I copy and pasted the code.

What's confusing me is that the code works on one spreadsheet but not the other. So clearly there's some important difference between the two that I'm missing.

Well, any other "fishing" is welcome... (I'll take your fishing over mine, any day).

Thanks for hanging in there with me. Let me know if you have any other thoughts.

Collen

 

[SkipVought]

Instead of ActiveSheet use the actual sheet tab name...

    Dim pvt As PivotTable, pvf As PivotField, pvi As PivotItem
    For Each pvt In [b]Sheets("My Sheet Name")[/b].PivotTables
        For Each pvf In pvt.PivotFields
            For Each pvi In pvf.HiddenItems
                pvi.Visible = True
            Next
        Next
    Next

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

Thanks but still same error...

I've done some debugging and I'm pretty sure its getting the values from the right sheet. Its like there's something (some setting maybe?) on the sheet that is keeping the .Visible property from being set.

Collen

 

[SkipVought]

Do you have Sheet protection on?

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

No...

 

[SkipVought]

Would you be willing to send me a reduced copy of your workbook?

1. Reduce the source data to several dozen rows

2. delete all other sheets except the source data and the sheet containing the pivot table.

skipandmary1017 at mindspring dot com

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[SkipVought]

BTW, if you choose to do this, make sure that the truncated model reproduces the error.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

Skip,
I sent it... thanks for the help.

Collen

 

[SkipVought]

Your DATA definition has problems. Consequently, your PT is corrupted.

You may be able to salvage the PT if you FIRST correct the DATA problems and then run the code the xlbo posted in the thread I referenced for you.

1. Isolate your DATA table from ALL other data. In your case the Date & Time in A2 & A3 is contiguous with DATA in A4 & B4. But there's another issue related to the next item in this list. Because I am recommending a DYNAMIC Named Range for DATA, NOTHING but headins & data must be in column A and row 4. So the data & time need to be in column B or greater and not in row 3.

2. Make the DATA range DYNAMIC using the OFFSET function via Insert/Name/Define. In the Names in workbook, enter DATA. In the Refers to enter the OFFSET function, typing everything EXCEPT THE REFERENCES. The references, you will enter by either selecting a cell, column or row as the cas may be. check out How can I rename a table as it changes size faq68-1331

3. Delete ALL empty cells BELOW your data and TO THE RIGHT of your data. For instance in the example that you sent me, delete rows from 21 to 65536 and delete columns W to IV. This is so that COUNTA, in the OFFSET function, will count the number of rows of heading and data and the number of columns of data.

4. This issue is not critical to the visible issue, but why do empty cells have TIC marks? This can affect certain numeric calculations since TIC indicates "" which is different than an EMPTY cell numerically.

CAVEAT: When using the OFFSET function to dynamically define a range, the row/column used for COUNTA, MUST have values in every cell in the range you are defining.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[bernie10]

Skip,
Thanks for the suggestion and for looking over the sheet. I'll definitely try these suggestions.

Thanks again,

Collen

 

[SkipVought]

You should change the macro for inserting tics

for each r in [Data]
  with r
    if trim(.value) = "" then
      .value = "'"
    end if
  end with
next

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTHFORTHWITH![/sub]