Running a macro when a cell in a range has changed

Q

QuietMan

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
 
B

Barb Reinhardt

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
 
Q

QuietMan

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


Barb Reinhardt said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top