How to stop Private sub from running whilst updating the sheet

[KNKN9]

Hi I have the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x   As Long
Dim LR  As Long
Dim C4  As Variant
    
C4 = Worksheets("P").Cells(4, 3).Value


Application.EnableEvents = False


With Worksheets("S")
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
For x = 1 To LR
    If .Cells(x, 2).Value = C4 Then .Cells(x, 7).Value = .Cells(x, 3).Value
    Next x
End With


Worksheets("S").Activate
Worksheets("S").Range("F1").End(xlDown).Select
Worksheets("S").Range(Selection, Selection.End(xlDown).Offset(0, 2)).copy
Worksheets("P").Range("B17").PasteSpecial xlPasteValues
Application.EnableEvents = True

what it is currently doing is once I run my sheet, the file updates. But when I work on the sheet it updates every time. It there a way where I can make the code run once and when I am updating the sheet it doesn't update repeatedly ?


Thanks in advanced

 

[Stiuart_W]

Why don't you change it to run when you open the file?

Otherwise you could have the code above set a cell to a particular value then check in the code and not run if the cell is that value.

 

[NoSparks]

The Worksheet_Change event is fired every time a cell value changes, provided the change is not via formula.
You can limit which cell fires the macro by specifying its address.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x   As Long
    Dim LR  As Long
    
If Target.Address = "$C$4" Then
    Application.EnableEvents = False
    
    With Worksheets("S")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
            For x = 1 To LR
                If .Cells(x, 2).Value = Target.Value Then .Cells(x, 7).Value = .Cells(x, 3).Value
            Next x
    End With

    Worksheets("S").Activate
    Worksheets("S").Range("F1").End(xlDown).Select
    Worksheets("S").Range(Selection, Selection.End(xlDown).Offset(0, 2)).Copy
    Worksheets("P").Range("B17").PasteSpecial xlPasteValues
    
    Application.EnableEvents = True
End If
End Sub

 

[KNKN9]

Because different people will use this file and the cell c4 is dependent on what the other columns will show.

Hope that makes sense.

Why don't you change it to run when you open the file?

Otherwise you could have the code above set a cell to a particular value then check in the code and not run if the cell is that value.

 

[Stiuart_W]

You need to be very specific in your request for help as it's not clear what you are doing/need. Eg you say "what it is currently doing is once I run my sheet, the file updates" - What do you mean by "run my sheet"?.

There are a lot of ways you could do what you want but unless you are clearer about when you want that code to run it's hard to help

 

[KNKN9]

Sorry for not being clear.
So at the moment my code excutes when the cell c4 has been updated. Which is what i want it to do. However, the problem is when i enter values on the sheet so updating the worksheet that the private sub code is on. The code excutes each time i update any given cell. I want to avoid this. So ideally i want it to run the code once cell c4 has been updated and does not excute whist im working on the sheet.

I hope that is cleaer

You need to be very specific in your request for help as it's not clear what you are doing/need. Eg you say "what it is currently doing is once I run my sheet, the file updates" - What do you mean by "run my sheet"?.

There are a lot of ways you could do what you want but unless you are clearer about when you want that code to run it's hard to help

 

[NoSparks]

You mean like the code in post 3 ?

 

[KNKN9]

So this is my code

 [COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]Dim x   As Long
Dim LR  As Long
Dim C4  As Variant
    
C4 = Worksheets("P").Cells(4, 3).Value


Application.EnableEvents = False


With Worksheets("S")
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
For x = 1 To LR
    If .Cells(x, 2).Value = C4 Then .Cells(x, 7).Value = .Cells(x, 3).Value
    Next x
End With


Worksheets("S").Activate
Worksheets("S").Range("F1").End(xlDown).Select
Worksheets("S").Range(Selection, Selection.End(xlDown).Offset(0, 2)).copy
Worksheets("P").Range("B17").PasteSpecial xlPasteValues [COLOR=#333333]Application.EnableEvents = True[/COLOR]

I have tried the code in post 3 and it doesn't do what i want as the worksheet stilk updates whilst im working on it.

 

[RoryA]

The code in post 3 will only run when you change C4, not any other cell.