Search code examples
excelvbapivot-tablerefresh

Refresh Pivot Table if Changes to Specific Column are made

I have the following code I found that will refresh the Pivot Table if any changes on the worksheet are made.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
  ActiveSheet.PivotTables("PromoList").RefreshTable
Application.EnableEvents = True

End Sub

The problem is that the worksheet has a lot of other fields I'll be editing but because the code applies to the whole worksheet, when I make any changes anywhere, I lose the ability to undo errors which could put me back quite a bit.

The data for the Pivot Table is contained in just a single column (A).

Is there a way to update the code to reference just column A so I don't lose Undo? Can I take it further and specify a range in column A?

Solution

  • This will trigger the refresh only if you made a change anywhwere in column A

    Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo EH

    Application.EnableEvents = False

    If Target.Column = 1 Then
        ActiveSheet.PivotTables("PromoList").RefreshTable
    End If

EH:
    Application.EnableEvents = True

End Sub