Macro in a Pivot Table

[bdh27]

Hi Everyone,


I'm new to Excel so please be gentle.


I'm trying to create a Macro that will allow me to change the values in a pivot table from quantity to net value. However, I keep getting a message that something is wrong the code below.


Macro1 Macro

'


'

Range("B7").Select

ActiveWorkbook.ShowPivotTableFieldList = True

ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Billing Qty"). _

Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _

"PivotTable2").PivotFields("Net Value"), "Count of Net Value", xlCount

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Net Value")

.Caption = "Sum of Net Value"

.Function = xlSum

End With

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1


I'm told that the problem is the following code: ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Billing Qty"). _

Orientation = xlHidden


However, I'm not sure why.


Any help would be greatly appreciated.


Thanks,

 

[Luke M]

Are you wanting to change the choice of a dropdown, or changing the layout of PivotTable (moving fields around). As you can see, when you record a macro, you foten get a lot of extra code that you don't really need. If we can narrow down to the specific line(s) that you need, we can delete all the rest. I've tried to clean the code a little and explain what each line is doing. Hopefully you can read it and figure out what line(s) we really want.

[pre]

Sub test()
Macro1 Macro
'

'
'This line shows the dialogue screen
ActiveWorkbook.ShowPivotTableFieldList = True

'Code shortcut so I don't have to retype everytime
With ActiveSheet.PivotTables("PivotTable2")
'Changing the orientation of a Data field
.PivotFields("Sum of Billing Qty").Orientation = xlHidden

'Add a field. Gives destination, field name, label, math operation
.AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Net Value"), "Count of Net Value", xlCount

'again, shortcut method
With .PivotFields("Count of Net Value")
'Change the caption, the text being displayed
.Caption = "Sum of Net Value"
'Change the math operation
.Function = xlSum
End With
End With
End Sub

[/pre]