1

I have the change macro below and I am trying to amend it so that it only runs where there is a change event in the sheet it's in and C2 of Previous is not 1. I can't seem to get it right (but I am a novice).

The code is in Sheet1 module. Sheet1 is refreshed frequently by external software and I exit the Worksheet_Change routine if it's not the data refresh I want. Price data is the refresh I want and is the first fresh it covers cell A1:P & whatever the last row is, that is 16 columns. The second refresh refreshes the remaining columns but I don't want the macro to be triggered by this event

Can anyone see what I've done wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub
    
    Application.EnableEvents = False
    
    If ThisWorkbook.Sheets("Previous").Range("C2").Value <> 1 Then Call InsertDetails

    Application.EnableEvents = True
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Andy
  • 509
  • 2
  • 7
  • 1
    **1.** Where have you placed the code? i.e which sheet? **2.** What is the objective of `If Target.Columns.Count <> 16 Then Exit Sub`? – Siddharth Rout Jun 01 '21 at 05:40
  • Hi Siddharth, it's in the Sheet1 module. Sheet1 is refreshed frequently by external software and I exit the Worksheet_Change routine if it's not the data refresh I want. Price data is the refresh I want and is the first fresh it covers cell A1:P & whatever the last row is, that is 16 columns. The second refresh refreshes the remaining columns but I don't want the macro to be triggered by this event. – Andy Jun 01 '21 at 05:48
  • 1
    You do not need `If Target.Columns.Count <> 16 Then Exit Sub`. Put this code `If Not Intersect(Target, Columns("A:P")) Is Nothing Then` and then inside this check for `If ThisWorkbook.Sheets("Previous").Range("C2").Value <> 1 Then Call InsertDetails`. You may want to see [THIS - Bottom of my post](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Jun 01 '21 at 05:55

0 Answers0