I have the following code which is intended to copy a column of cells and paste them up one position, triggered by a cell val. The problem is the code runs multiple times and performs the copy and paste multiple times. I have tried to insert a 1 second pause in the code to give time for the trigger cell to return to false, but it does not seem to work.
Private Sub Worksheet_Calculate()
Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("D104")
For Each rng In Inrange.Cells
If Not IsError(rng.Value) Then
If Me.Range("D104").Value = "1" Then
' NI_LOG Macro
Range("C3:C102").Select
Range("C102").Activate
Selection.Copy
Range("C2").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'Wait 1 second for to avoid multiple logging
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Else
If Me.Range("D104").Value = "2" Then
' PE_Log Macro
Range("F3:F102").Select
Range("F102").Activate
Selection.Copy
Range("F2").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'Wait 1 second for to avoid multiple logging
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End If
End If
End If
Next rng
End Sub
Bookmarks