CODEWITHSUNDEEP
excelvbapivot-tablerefresh
GigaFluxx
GigaFluxx

Reputation: 31

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?

Upvotes: 0

Views: 472

Answers (1)

Storax
Storax

Reputation: 12167

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

Upvotes: 1

Related Questions