Auto run a macro when cell value changes

[Tanya0806]

Hi,

I have a workbook which has 2 sheet: DATA and PIVOT
DATA: It has a named range table whose size changes dynamically. The table gets data from the database.
PIVOT : It has a pivot table whose source is the table in DATA sheet.

I have a macro to copy a formula in one of the columnin the table. This macro dynamically copy the formulas till the last row in the table. I need to automate this macro after data is inserted into the table and also automate the refresh of Pivot table.

For this I am using a NOW() function in cell P2 which gets updated after every insert into the table and use the following code to automate:
Private Sub Worksheet_Calculate()
Dim iLastrow As Long
If Range("P2").Value > 0 Then
Application.EnableEvents = False
MsgBox "Rows Inserted"
iLastrow = Range("A1").SpecialCells(xlCellTypeLastCell).Row

Range("N2").Copy Destination:=Range(Range("N2"), Range("N" & iLastrow))


Worksheets("PIVOT").PivotTables("PivotTable1").PivotCache.Refresh
Range("Q1") = ""
Application.EnableEvents = True
End If
End Sub

This works well if I double click on cell P2 but I want it to happen automatically. Please let me know if I am missing something in my code.

Thanks,
Tanya

[dilipandey]

HI Tanya,

welcome to the forum.

First, suggest you to follow code tags and also find time to go through with forum rules:-

In order to run a macro when cell values changes, you can use the sheet see the forum rules You can use "selection change" sheet event. Thanks.


Regards,
DILIPandey
<click on below * if this helps>