Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Un-pivot
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Un-pivot
Tom Ogilvy wrote: 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 Thanks, that should save me lots of work. " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - Editing the data source shared by multiple pivot ta | Excel Discussion (Misc queries) | |||
two pivot q's - get rid og getpivotdata and pivot based on pivot | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |