Hello,
I am new comer to Excel Forum family. I have gone through and get the VBA code from Anne to change value field of pivot table (Pivot table contain 6 value fields for Sum of)
The code allow user to select one month only. I modified the code so that i can select more than 2 months but could not.
Can anyone review attached file and advise?
[Private Sub CommandButton1_Click()
On Error Resume Next
Dim pf As PivotField
Dim month As String
Dim curm As String
For Each pf In Sheet3.PivotTables("PivotTable1").DataFields
curm = pf.Name
Next pf
month = Sheet2.Range("B10")
If month = Right(curm, 6) Then Exit Sub
Application.ScreenUpdating = False
Sheet3.PivotTables("PivotTable1").AddFields RowFields:=Array("Supplier", _
"RM Name")
Sheet3.PivotTables("PivotTable1").PivotFields(curm). _
Orientation = xlHidden
With Sheet3.PivotTables("PivotTable1").PivotFields(month)
.Orientation = xlDataField
.Caption = "Sum of " & month
.Function = xlSum
.NumberFormat = "[$$-409]#'##0.00"
End With
Sheet3.PivotTables("PivotTable1").PivotFields("Data").PivotItems( _
"Sum of " & month).Position = 1
Application.ScreenUpdating = True
Application.Goto Sheet3.Range("A3")
End Sub
][/QUOTE]
Thanks,
Nhuttrung
Bookmarks