Pivot table field settings
Hi
I have searched the web but couldn't find what I was after. I am trying to write a macro that will change all the fileds selected in a range of a pivot table. The following is a macro I recorded to get an idea of what I need to do. Range("C4").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sum of b") .Function = xlCount .NumberFormat = "0.00" End With How do I tell what pivottable name and assoociated Pivotfields name is the active cell refering to or the instance within the current selected range. I assume I would use the current selection object looping through each cell until it reaches the end. ie for each .... in selection With ActiveSheet.PivotTables(?).PivotFields(?) .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With Any help would be appreciated Regards Greg |
Pivot table field settings
You can use code similar to the following:
Sub FormatSelection() Dim c As Range Dim rng As Range Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set rng = Selection For Each c In rng Set pf = c.PivotField If Not pf Is Nothing Then With pf .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With End If Next c End Sub Greg wrote: Hi I have searched the web but couldn't find what I was after. I am trying to write a macro that will change all the fileds selected in a range of a pivot table. The following is a macro I recorded to get an idea of what I need to do. Range("C4").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sum of b") .Function = xlCount .NumberFormat = "0.00" End With How do I tell what pivottable name and assoociated Pivotfields name is the active cell refering to or the instance within the current selected range. I assume I would use the current selection object looping through each cell until it reaches the end. ie for each .... in selection With ActiveSheet.PivotTables(?).PivotFields(?) .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With Any help would be appreciated Regards Greg -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Pivot table field settings
Thankyou Debra
It worked a treat, will save myself and others a lot of time. Regards Greg Debra Dalgleish wrote: You can use code similar to the following: Sub FormatSelection() Dim c As Range Dim rng As Range Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set rng = Selection For Each c In rng Set pf = c.PivotField If Not pf Is Nothing Then With pf .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With End If Next c End Sub Greg wrote: Hi I have searched the web but couldn't find what I was after. I am trying to write a macro that will change all the fileds selected in a range of a pivot table. The following is a macro I recorded to get an idea of what I need to do. Range("C4").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sum of b") .Function = xlCount .NumberFormat = "0.00" End With How do I tell what pivottable name and assoociated Pivotfields name is the active cell refering to or the instance within the current selected range. I assume I would use the current selection object looping through each cell until it reaches the end. ie for each .... in selection With ActiveSheet.PivotTables(?).PivotFields(?) .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With Any help would be appreciated Regards Greg |
Pivot table field settings
You're welcome! Thanks for letting me know that it helped.
Greg wrote: Thankyou Debra It worked a treat, will save myself and others a lot of time. Regards Greg Debra Dalgleish wrote: You can use code similar to the following: Sub FormatSelection() Dim c As Range Dim rng As Range Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set rng = Selection For Each c In rng Set pf = c.PivotField If Not pf Is Nothing Then With pf .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With End If Next c End Sub Greg wrote: Hi I have searched the web but couldn't find what I was after. I am trying to write a macro that will change all the fileds selected in a range of a pivot table. The following is a macro I recorded to get an idea of what I need to do. Range("C4").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sum of b") .Function = xlCount .NumberFormat = "0.00" End With How do I tell what pivottable name and assoociated Pivotfields name is the active cell refering to or the instance within the current selected range. I assume I would use the current selection object looping through each cell until it reaches the end. ie for each .... in selection With ActiveSheet.PivotTables(?).PivotFields(?) .Function = xlSum .NumberFormat = "$#,##0.00;[Red]-$#,##0.00" End With Any help would be appreciated Regards Greg -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 09:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com