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

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

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

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