Bonjour,
Est-ce que quelqu'un peut-il m'expliquer ce code et comment l'intégrer 'si correct) dans mon PT?
Il permettrait d'ajouter une colonne dans un PIVOT Table afin d'insérer des commentaires et de les mémoriser.
Je ne suis pas spécialiste VBA,
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ShComments As Worksheet
Dim ColCustomer As Integer
Dim ColComments As Integer
Dim r As Long
If Target.Count > 1 Then Exit Sub
With PivotTables("Pivot All GBP")
ColCustomer = .PivotFields("Customer").LabelRange.Column
With .TableRange1
ColComments = .Columns.Count - .Column + 2
End With
End With
If Application.Intersect(Target, Columns(ColComments)) Is Nothing Then Exit Sub
On Error Resume Next
Set ShComments = Worksheets("Comments")
If Err <> 0 Then
Err.Clear
Set ShComments = Worksheets.Add(After:=Me)
Me.Activate
With ShComments
ShComments.Name = "Comments"
.Cells(1, 1).Value = "Customer"
.Cells(1, 2).Value = "Comment"
End With
End If
On Error GoTo 0
With ShComments
If WorksheetFunction.CountIf(.Columns(1), Cells(Target.Row, ColCustomer).Value) > 0 Then
r = WorksheetFunction.Match(Cells(Target.Row, ColCustomer).Value, .Columns(1), False)
Else
r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(r, 1).Value = Cells(Target.Row, ColCustomer).Value
End If
.Cells(r, 2).Value = Target.Value
End With
End Sub
Private Sub Worksheet_Calculate()
Dim ShComments As Worksheet
Dim RngCustomer As Range
Dim ColComments As Integer
Dim Cell As Range
Dim r As Long
On Error Resume Next
Set ShComments = Worksheets("Comments")
If Err <> 0 Then
Err.Clear
Exit Sub
End If
On Error GoTo 0
Application.EnableEvents = False
With PivotTables("Pivot All GBP")
Set RngCustomer = .PivotFields("Customer").DataRange
With .TableRange1
ColComments = .Columns.Count - .Column + 2
End With
End With
With ShComments
For Each Cell In RngCustomer
If WorksheetFunction.CountIf(.Columns(1), Cell.Value) > 0 Then
r = WorksheetFunction.Match(Cell.Value, .Columns(1), False)
Cells(Cell.Row, ColComments).Value = .Cells(r, 2).Value
Else
Cells(Cell.Row, ColComments).Value = ""
End If
Next Cell
End With
Application.EnableEvents = True
End Sub
When comments are added the Worksheet_Change event procedure stores them in a new worksheet. When the pivot table is refreshed the Worksheet_Calculate event procedure reads the values form that sheet and updates them.
Merci