Pivot Table PivotField Position IF statement

stlciao

New Member
Joined
Nov 10, 2002
Messages
5
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..
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Wouldn't it be simpler to have the command button disappear if it has been previously chosen?
 

stlciao

New Member
Joined
Nov 10, 2002
Messages
5
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,140
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top