Un-pivot

  • Thread starter Thread starter matts2
  • Start date Start date
M

matts2

I have several large pivot tables that really should not be pivot
tables. All they do is filter. I would like to do 1 of 2 things. Either
somehow convert these to just filters or to make a VBA program that
does the filtering. (That last option makes sense since all we do with
the table is convert some data to an output text file with a macro. So
why not do it all in the macro.) So, anyway, here are my questions:

1) How can I convert the tables to regular filtered sheets?

2) How can I, other than hand typing, capture the filter choices? There
are scores and scores of these selected and I will certainly mess up if
I just hand copy. Is there VBA access to this?

TIA
 
Tom said:
You can make a pivot table just cells containing data by selecting the whole
table and doing Edit=>Copy, then immediatly doing Edit=>Paste Special and
select Values.

This however is not a filter - it is just unchangeable results of applying a
"filter". there is no built in way to transform a pivot table into a
filtered copy of the original data.

In vba, you can loop through the items of the pivotTable

Sub abc()
Dim sh as worksheet, pTbl as PivotTable
Dim pFld as PivotField, pitm as PivotItem
Set sh = Worksheets("Sheet3")
Set pTbl = sh.PivotTables("PivotTable1")
Set pFld = pTbl.PivotFields("Header2")
For Each pitm In pFld.PivotItems
Debug.Print pitm.Value, pitm.Visible
Next
End Sub

produced:

B True
C False
A True
D False

The true items were selected in the dropdown
Click to expand...

Thanks, that should save me lots of work.
 
Back
Top