Private Sub Worksheet_Change(ByVal Target As Range)
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Dim vMatch
Dim sSuffix As String
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("e13:k14")) Is Nothing Then Exit Sub
If LenB(Range("e13").Value2) = 0 Then Exit Sub
On Error GoTo reset_all
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
vMatch = Application.Match(Range("e13").Value, Range("B1:Q1"), 0)
If Not IsError(vMatch) Then
'Here you amend to suit your data
Set pt = Me.PivotTables(1)
With pt
.ManualUpdate = True
For Each Field In .DataFields
Field.Orientation = xlHidden
Next Field
If vMatch = 1 Then
sSuffix = vbNullString
Else
sSuffix = Int((vMatch + 1) / 2)
End If
.AddDataField Field:=.PivotFields("Target" & sSuffix), Caption:="Target ", Function:=xlSum
.PivotFields("Target ").Calculation = xlRunningTotal
.AddDataField Field:=.PivotFields("Actual" & sSuffix), Caption:="Actual ", Function:=xlSum
.PivotFields("Actual ").Calculation = xlRunningTotal
.ManualUpdate = False
End With
End If
reset_all:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub