Hi Clint
Can you attach the sample file
On Wed, Sep 21, 2011 at 11:08 PM, Clint <clinthjack...@gmail.com> wrote:
> This one has been driving me crazy. VBA seems to have a problem
> removing calculated fields. See code below.
>
>
> Sub Value_Click()
>
> ''
> Application.ScreenUpdating = False
> Application.Calculation = xlManual
> '
> Dim PF As PivotField
> Dim pfNew As PivotField
> Dim Field As String
> Dim label As String
> Dim PT As PivotTable
> Dim strSource As String
> Dim strFormula As String
> '
> Set PT = ActiveSheet.PivotTables("da_p")
> Field = "ext_price"
> label = "$"
>
> For Each PF In PT.CalculatedFields
> If PF.Name = Field Then
> GoTo 0
> Else
> strSource = PF.SourceName
> strFormula = PF.Formula
> PF.Delete
> Set pfNew = PT.CalculatedFields.Add(strSource, strFormula)
> GoTo 1
> End If
> Next PF
>
> 1
> With PT
> .AddDataField PT.PivotFields(Field), label, xlSum
> .PivotFields(label).NumberFormat = "_($* #,##0_);_($*
> (#,##0);_($* ""-""_);_(@_)"
> End With
> 0
> ''
> Application.Calculation = xlAutomatic
> Application.ScreenUpdating = True
>
> End Sub
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
