Worksheet Change Event- Call Macro

[davidhall]

I am trying to have a macro run automatically when the worksheet titled "Index" cell "A1" is changed. A1 references another worksheet that users change so it has a formula in it.


In the View Code of the Index sheet, I have the following code:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "A1" Then
Application.EnableEvents = False

Call IndexDataExtract

Application.EnableEvents = True

End If


End Sub



The macro that I need to run upon cell A1 changing is called IndexDataExtract.

The Vba for IndexDataExtract is placed in a module.

The code is as follows:



Option Explicit
Option Compare Text
Sub IndexDataExtract()


Dim i As Long
Dim FinalRow As Long
Dim NextRow As Integer

Application.ScreenUpdating = False
FinalRow = Worksheets("IndexImport").Cells(Rows.Count, 2).End(xlUp).Row
NextRow = Worksheets("Index").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("Index").Range("A3:D500").ClearContents
NextRow = 3
Worksheets("IndexImport").Activate
For i = 1 To FinalRow
If Cells(i, 2).Value = Worksheets("Index").Range("A1").Value Then
Cells(i, 2).Resize(1, 4).Copy Destination:=Worksheets("Index").Cells(NextRow, 1)
NextRow = NextRow + 1
End If
Next i
Worksheets("Index").Activate

'Filtering Column C which is the index date, from the most recent to oldest

Rows("3:3").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Index").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Index").AutoFilter.Sort.SortFields.Add Key:=Range( _
"C3"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Index").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter

Application.ScreenUpdating = True

End Sub


I have tried placing the IndexDataExtract code in ThisWorkbook and in the view code section with the private sub code but cant seem to get it to work.

Any suggestions?

 

[VoG]

Maybe try this

Private Sub Worksheet_Calculate()
Static OldVal As Variant
With Application
    If Range("A1").Value <> OldVal Then
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        OldVal = Range("A1").Value
        Call IndexDataExtract
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End If
End With
End Sub

 

[davidhall]

Hmm..didn't really change but still works