Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating reports per Pivot Table PivotItem
I have a pivot table that cna be used to create reports per filtered PIVOT
ITEM. my code jams when it trys to set the visible properties. Can you help? Sub CreatePivotTabs() 'Create tabs per Pivot table Dim x, y As Integer Dim strLOB_NM, strHideLOB_NM As String Sheets("Pivot Table").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("LOB") For x = 1 To .PivotItems.Count For y = 1 To .PivotItems.Count If y < x Then strHideLOB_NM = .PivotItems(y).Name .PivotItems(strHideLOB_NM).Visible = False Else .PivotItems(y).Visible = True strLOB_NM = .PivotItems(y).Name End If Next y 'create report and create on New tab named via strLOB_NM ' FormatAuditIssueTables Sheets("Pivot Table"), strLOB_NM Sheets("Pivot Table").Select Next x End With End Sub Thank you so much! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating reports per Pivot Table PivotItem
I modified the code to match what I could in the stmaents foud , but it still
jams on setting the visible to false. I want to loop through each 'LOB' and create a report based on the table. Can anyone shed some light on this? 'Create tabs per Pivot table Dim x, y As Integer Dim strLOB_NM, strHideLOB_NM As String Dim intASO As Integer Sheets("Pivot Table").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("LOB") intASO = .AutoSortOrder .AutoSort xlManual, "LOB" For x = 1 To .PivotItems.Count For y = 1 To .PivotItems.Count If y < x And .PivotItems(y).Visible = True Then strHideLOB_NM = .PivotItems(y).Name .PivotItems(strHideLOB_NM).Visible = False '.PivotItems(y).Visible = False ElseIf y = x Then .PivotItems(y).Visible = True strLOB_NM = .PivotItems(y).Name End If Next y ' FormatAuditIssueTables Sheets("Pivot Table"), strLOB_NM Sheets("Pivot Table").Select Next x .AutoSort intASO, "LOB End With End Sub "Candyman" wrote: I have a pivot table that cna be used to create reports per filtered PIVOT ITEM. my code jams when it trys to set the visible properties. Can you help? Sub CreatePivotTabs() 'Create tabs per Pivot table Dim x, y As Integer Dim strLOB_NM, strHideLOB_NM As String Sheets("Pivot Table").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("LOB") For x = 1 To .PivotItems.Count For y = 1 To .PivotItems.Count If y < x Then strHideLOB_NM = .PivotItems(y).Name .PivotItems(strHideLOB_NM).Visible = False Else .PivotItems(y).Visible = True strLOB_NM = .PivotItems(y).Name End If Next y 'create report and create on New tab named via strLOB_NM ' FormatAuditIssueTables Sheets("Pivot Table"), strLOB_NM Sheets("Pivot Table").Select Next x End With End Sub Thank you so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating worksheet reports from a pivot table | Excel Worksheet Functions | |||
Pivot Table Reports | Excel Discussion (Misc queries) | |||
Pivot table reports cannot overlap another pivot table | Excel Programming | |||
Problem with Pivot table reports | Excel Programming | |||
MS Excel - How can I summarize a table? Will Pivot Table Reports do this for me? | Excel Programming |