Hi,

I realy need some help. I am new to the forum so hopefully I am providing this information in the right way.

I have some VBA code which I found through good old google which is driving me round the bend.

The code is written so that the user can press a button, the macro runs and prompts the user to input a number which relates to the number of weeks they have selected the report to present data for. The macro then deletes existing calculated fields in the pivot and then adds in the calculated fields again with the new number of weeks in the formula. The problem is that in the delete process it deletes all calculated fields in all the sheets in the workbook. As my graphs read off dynamic ranges this is causing errors elsewhere. I have tinkered and tinkered. Please can someone help.

Code below including 'greened out bits which I was tinkering with.

Thanks in advance for your help.

Sandra

Sub ChngPivot()

  
  Application.EnableEvents = False
  Dim pvt As PivotTable
  Dim pvf As PivotField
  Dim strSource As String
  Dim strFormula As String
  Dim wk As Long
    Set pvt = ActiveSheet.PivotTables(1)
    On Error Resume Next
   
    On Error GoTo 0
    If pvt Is Nothing Then
        MsgBox "No PivotTable selected", vbInformation, "Oops..."
        Exit Sub
    End If
  'pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone
  'pvt.PivotCache.Refresh
    
    Application.DisplayAlerts = False
        wk = Application.InputBox _
        (Prompt:="Enter Number Weeks :", _
            Title:="by_grade_table", Type:=1)
        On Error GoTo 0
    Application.DisplayAlerts = True
    Set pvt = ActiveSheet.PivotTables(1)
    'For Each pvf In pvt.CalculatedFields
    'strSource = pvf.SourceName
    'strFormula = pvf.Formula
    'pvf.Delete
    'Next pvf
  'On Error Resume Next
 
  ActiveSheet.PivotTables("PivotTable1").PivotFields("CROS_Listed").Delete
  ActiveSheet.PivotTables(1).PivotFields("CROS_Actual").Delete
  
  ActiveSheet.PivotTables(1).CalculatedFields.Add "CROS_Actual", _
  "= (RSV_/" & wk & ")/(No_Stores/" & wk & ")", True
            
   With ActiveSheet.PivotTables(1).PivotFields("CROS_Actual")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "£#,##0.00"
    .Caption = "CROS Actual"
    .Position = 3
End With

ActiveSheet.PivotTables(1).CalculatedFields.Add "CROS_Listed", _
  "= (RSV_/" & wk & ")/(No_Listed/" & wk & ")", True
            
   With ActiveSheet.PivotTables(1).PivotFields("CROS_Listed")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "£#,##0.00"
    .Caption = "CROS Listed"
    
End With
  Application.EnableEvents = True

    

End Sub