Pivot Table PivotField Position IF statement

[stlciao]

I have a large Pivot Table report that uses up to 8 page views. It has 4 standard views that the user controls with code in command buttons. I am looking for a way that if the user changes the view, but a pivotfield is in the correct position, that it will not recalculate the pivot table, as this can take up to 30 seconds. I would like to have an IF statement that would first check to see it the pivotfield is in the correct position before it moves it to the correct position.

The below code does not work, but this is what I am trying to accomplish:

If With ActiveSheet.PivotTables("Pivot1").PivotFields"City")_
.Orientation = xlPageField.Position <> 6 Then
With ActiveSheet.PivotTables("Pivot1").PivotFields("City")
.Orientation = xlPageField
.Position = 6
End With

This is just one example as the report would change the position of various PivotFields to different PageField and RowField positions.

Any thoughts? Thanx for looking..

 

[tactps]

Wouldn't it be simpler to have the command button disappear if it has been previously chosen?

 

[stlciao]

No, becauase this is a report templete that is refreshed everyday and sometimes the user will go back and forth between views. I only provided a sample of the code, the actual codes looks like this:

Private Sub DVDDailySegment_Click()
' View by Segment DVD Daily Variance Report

Application.ScreenUpdating = False

With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Product Segment")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Owner Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Title Description")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("National Account")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Marketing Owner")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("T East")
.Orientation = xlPageField
.Position = 2
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Sub Brand")
.Orientation = xlPageField
.Position = 3
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Brand")
.Orientation = xlPageField
.Position = 4
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Segment")
.Orientation = xlPageField
.Position = 5
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Franchise")
.Orientation = xlPageField
.Position = 6
End With
With ActiveSheet.PivotTables("DVDDailyPivot").PivotFields("Business Unit")
.Orientation = xlPageField
.Position = 7
End With
'Collapse/Un-Collapse
On Error Resume Next
Range("d18").Select
Selection.ShowDetail = False
Range("c18").Select
Selection.ShowDetail = False
Range("B18").Select
Selection.ShowDetail = False
Range("A18").Select
Selection.ShowDetail = True

'Column Widths
Columns("A:A").ColumnWidth = 25
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 20
Columns("D:D").ColumnWidth = 20
ActiveWindow.ScrollRow = 1

Range("A18").Select

End Sub