Running a macro when a cell in a range has changed
 

Dose anyone know how to write this code....I need to refresh a pivottable
each time Cell A4 or A6 is changed...I tried the code below but it's not
working only updates on selection the cell rather that after the change has
been made

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$4:$A$6" Then
Application.ScreenUpdating = False
TT = Range("Tab_Name")
Sheets(TT).Select
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
TR = Range("Rpt_Tab")
Sheets(TR).Select
Application.ScreenUpdating = True
End If
End Sub

--
Helping Is always a good thing

Running a macro when a cell in a range has changed
 

First, you need to use Worksheet_Change, not Worksheet_SelectionChange

Replace the first two lines with this and pull out the end if.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Excel.Range
Set myRange = Union(Me.Range("A4"), Me.Range("A6"))

If Target.Count 1 Then Exit Sub
If Intersect(Target, myRange) Is Nothing Then Exit Sub

Also, add Option Explicit before your code and it will force you to declare
all variables. Believe me, it'll help you out in the long run.

HTH,
Barb Reinhardt


"QuietMan" wrote:

Dose anyone know how to write this code....I need to refresh a pivottable
each time Cell A4 or A6 is changed...I tried the code below but it's not
working only updates on selection the cell rather that after the change has
been made

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$4:$A$6" Then
Application.ScreenUpdating = False
TT = Range("Tab_Name")
Sheets(TT).Select
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
TR = Range("Rpt_Tab")
Sheets(TR).Select
Application.ScreenUpdating = True
End If
End Sub

--
Helping Is always a good thing

Running a macro when a cell in a range has changed
 

Thanks...
--
Helping Is always a good thing


"Barb Reinhardt" wrote:

First, you need to use Worksheet_Change, not Worksheet_SelectionChange

Replace the first two lines with this and pull out the end if.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Excel.Range
Set myRange = Union(Me.Range("A4"), Me.Range("A6"))

If Target.Count 1 Then Exit Sub
If Intersect(Target, myRange) Is Nothing Then Exit Sub

Also, add Option Explicit before your code and it will force you to declare
all variables. Believe me, it'll help you out in the long run.

HTH,
Barb Reinhardt


"QuietMan" wrote:

Dose anyone know how to write this code....I need to refresh a pivottable
each time Cell A4 or A6 is changed...I tried the code below but it's not
working only updates on selection the cell rather that after the change has
been made

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$4:$A$6" Then
Application.ScreenUpdating = False
TT = Range("Tab_Name")
Sheets(TT).Select
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
TR = Range("Rpt_Tab")
Sheets(TR).Select
Application.ScreenUpdating = True
End If
End Sub

--
Helping Is always a good thing