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!

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!