Private Sub Workbook_Open()
Set App = Application
'Application.ReferenceStyle = xlA1
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sClickedFieldName As String
Dim sPivotUniqueKey As String
Dim PT As Excel.PivotTable
Dim SourceDataSheet As String
Dim sSheetTrim As String
Dim sRangeTrim As String
Dim dataArea As Range
On Error Resume Next
'# Name of the pivot table major key
sPivotUniqueKey = "Request item"
Set PT = Target.Item(1).PivotTable
If Err.Number <> 0 Then GoTo Jump_01
'# Get clicked field name
sClickedFieldName = Sh.Cells(PT.TableRange1.Rows(1).Row, Target.Column)
Debug.Print sClickedFieldName
'# Get cliked record unique key
sClickedRecordKey = Sh.Cells(Target.Row, PT.TableRange1.Rows(1).Find(sPivotUniqueKey).Column)
Debug.Print sClickedRecordKey
'# It could be managed alternatively considering first field of pivot table if it's always the unique key
'# Get pivot range in variable dataArea
rgDataSource = Replace(PT.SourceData, "L", "R")
rgDataSource = Replace(Application.ConvertFormula( _
Formula:=rgDataSource, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1, _
toAbsolute:=xlAbsolute), _
"[" & ThisWorkbook.Name & "]", "")
Set dataArea = Range(rgDataSource)
'# Get pivot worksheet
Set shDataSource = dataArea.Parent
'' sSheetTrim = Split(SourceDataSheet, "!")(0)
'' sSheetTrim = Split(SourceDataSheet, "'")(1)
'' sSheetTrim = Split(sSheetTrim, "]")(1)
'some trimming to get desired references for lastRow (sheet in which the SourceData are located) and dataArea (starting cell and last column of SourceData)
'' sRangeTrim = Left(dataArea.Address, 8)
'# Determine the cell to be updated in the data source
lDataSourceKeyColumn = shDataSource.Rows(1).Find(sPivotUniqueKey).Column
lDataTargetColumn = shDataSource.Rows(1).Find(sClickedFieldName).Column
lDataSourceRecordRow = shDataSource.Cells(1, lDataSourceKeyColumn).EntireColumn.Find(sClickedRecordKey).Row
Set rgDataSourceTargetField = dataArea.Cells(lDataSourceRecordRow, lDataTargetColumn)
'# Update data source (if it's not a field with formula)
If shDataSource.Range(rgDataSourceTargetField.Address).HasFormula = False Then
shDataSource.Range(rgDataSourceTargetField.Address).Value = Target.Item(1).Value
End If
Jump_01:
End Sub