Update Pivot data with VBA

Hi Luka and welcome to the Forum

I hope I've understood your question correctly...

Your existing macros were recorded using the Record Macro feature - I have not tried to optimise them but in the attached revised file, I offer a solution which leaves them in tact and working.

Excel has "events" (search the Tutorials section on that) and we can use the Worksheet_Change event- that triggers the macro below if a value is changed (or re-entered) in your Data sheet (the code is behind that sheet in VB Project Explorer).

I've commented the code to explain what happens but essentially it checks if a data value was changed. If so it runs your two macros and moves to the Pivot sheet to show the new Top 10.

Private Sub Worksheet_Change(ByVal Target As Range)

' do nothing it a cell outside the data table is changed
If Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

'prevent re-triggering this macro...
Application.EnableEvents = False
' ... and screen swapping
Application.ScreenUpdating = False


'run the clear and top10 macros
Call Clear
Call Top_10
' show new pivot
Worksheets("Pivot").Activate
'restore
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Currently your data sheet is left in the filtered mode but my version uses a workbook event (triggered when sheets are selected) to clear the filter is the user returns to the Data sheet. Here's the code (under ThisWorkbook in VB Project Explorer):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name = "Data" And Sh.AutoFilterMode = True Then
    ' if data sheet re-selected, clear filters
    Sh.AutoFilterMode = False
End If

End Sub

That will leave the Pivot sheet unchanged (but you can delete it using your Clear button).

REVISION:

Given your additional requirement to copy the PivotTable to the sheet "Top10", in the second attached file, I've extended the code above to do that. Changes and comments are in bold:

Private Sub Worksheet_Change(ByVal Target As Range)

' do nothing it a cell outside the data table is changed
If Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

'prevent re-triggering this macro...
Application.EnableEvents = False
' ... and screen swapping
Application.ScreenUpdating = False


'run the clear and top10 macros
Call Clear
Call Top_10

'clear and populate Top10 sheet
With Worksheets("Top10")
    .UsedRange.Clear
    ' copy new pivot
    Worksheets("Pivot").PivotTables("PivotTable1").TableRange2.Copy
    'paste values to clean sheet
    .Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    'adjust some values and add borders
    .Range("A1:B1") = Array("Name On Card", "Tx Total")
    .Columns(2).NumberFormat = "#,##0.00"
    .Columns("A:B").AutoFit
    .UsedRange.Borders.LineStyle = xlContinuous
    ' show Top10
    .Activate
    .Range("D1").Select
End With

'restore
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Note that it would be nice to hide your "Pivot" sheet but your recorded macro prevents that (and would take a lot of effort to mofiy that to avoid selecting ranges- which your Top_10 macro deos a lot).

Hope this fixes your problem- if so, please remember to mark this Answer as Selected.