Event generated macro not working

[ALAMZEB]

Hi.
This Macro works well when i run it but i am trying to run it continuously with out pressing macro button. I tries to use following code but it doesnt update itself

Private Sub Consolidate()

Worksheets("Claims").Range("AM16").Consolidate Sources:= _
  "Claims!R6C12:R10000C14" _
  , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub

where its getting data from column L & N and giving consolidated result in AM16


thanks guys

 

[Hui]

What do you mean by run continuously ?
Do you mean if the range Claims!L6:N10000 changes?

If that is the case then I would add an event handler to teh worksheet in question:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L6:N10000")) Is Nothing Then Exit Sub

Worksheets("Claims").Range("AM16").Consolidate _
  Sources:="Claims!R6C12:R10000C14", _
  Function:=xlSum, _
  TopRow:=False, _
  LeftColumn:=True, _
  CreateLinks:=False
End Sub

 

[ALAMZEB]

Hi Hui

Above code doesnt updates value itelf
after input the consolidate list doesnt update but if i delete consolidate list and than input numbers in column L & N than only it updates

 

[ALAMZEB]

attcahed

 

[Hui]

Can you please describe what it isn't doing and what it should do ?

 

[ALAMZEB]

Hi Hui

Comments in sheet

 

[Hui]

Change the code on the Sheet13 (Claims) code module to be:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("L6:N10000")) Is Nothing Then Exit Sub

'Clear old data
Range("AM16").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
   
'Consolidate
Worksheets("Claims").Range("AM16").Consolidate _
  Sources:="Claims!R6C12:R100C14", _
  Function:=xlSum, _
  TopRow:=False, _
  LeftColumn:=True, _
  CreateLinks:=False
End Sub

get rid of all the other code in this code module