vba activate macro when cell changes in Excel

Q174. In Excel, how can I activate a routine when there is a change in value of a cell?

For example, I want to filter records pertaining to the person name specified in cell AV2. Whenever name in AV2 changes, I want to apply filter based on name selected and then copy the filtered data and paste it in cell AT4.

excel vba activate macro when cell changes

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Private Sub WorkSheet_Change(ByVal Target As Range)
  3. Dim KeyCells As Range
  4. Dim sName As String
  5. ''
  6. Set KeyCells = ActiveSheet.Range("$AV$2")
  7. If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
  8. ''
  9. sName = ActiveSheet.Range("$AV$2").Value
  10. ''
  11. 'Clear existing data in Destination.
  12. ActiveSheet.Range("AT4:AV14").Clear
  13. ''
  14. 'Filter rows based on Name which is Field 2 (Col AQ).
  15. ActiveSheet.Range("AP4:AR4").AutoFilter
  16. ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
  17. ''
  18. 'Copy filtered table and paste it in Destination cell.
  19. ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Copy
  20. ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
  21. Application.CutCopyMode = False
  22. ''
  23. 'Remove filter that was applied.
  24. ActiveSheet.AutoFilterMode = False
  25. End If
  26. End Sub

Description:

a) The above code has to be included in the module of the "Sheet" in which the input Table resides.

b) Line 2 - Whenever there is change in any cell in the sheet, this routine is executed.

c) Line 7 - Check if the change in WorkSheet pertains to cell of interest (AV2 in this case). If yes, execute code to filter, copy filtered data, paste it in destination and remove filter (Lines 7-23).

Result after entering "Brad" in cell AV2 (automatic update of table in AT4):

excel excel vba activate macro when cell changes

 

You can find similar Excel Questions and Answer hereunder

1) How can I turn off Alerts using VBA?

2) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

3) How can I identify the cells that are dependent on a particular cell?

4) Vba clear the contents of an entire sheet in Excel

5) How can I enter multiple lines of data in a cell?

6) How can I add and link a Chart Title to a cell value?

7) How to concatenate strings in vba in Excel

8) How can I dynamically add a hyperlink using VBA?

9) How can I set up ListBox using VBA to allow users to select multiple values?

10) How can I filter and copy only filtered data using VBA?

11) How can I loop through all WorkSheets and get their names using VBA?

12) How do I use Find to determine last occurrence of a string in a WorkSheet range using VBA?

13) I want to pull data from a table using a reference cell in table. Is there a function to support that?

14) Excel 2010 vba replacement of application filesearch in Excel

15) How can I set FreezePanes in a certain range using VBA?