Copy and Paste Value

[erichmiller]

Hello Everyone,

I have values of what I have in stock in cells C7:C22.

I have a formula in cells H7:H22 that calculate start weight minus end weight when are entered into cells D7:D22 and E7:E22.

If the value changes in Column H which indicates we used some of the stock on hand I want the new value to be pasted into column c and the numbers entered on columns d and e to be deleted.

I have tried several things but cannot get it to run properly. Here is one of the thing sI have tried below

If E7>0 then
range("H7").Select
range("H7").Copy
range("C7").Paste
Range("E7").ClearContents
Range("D7").ClearContents
Else
End If

 

[njimack]

I think this is what you're after. Paste it into the module of the sheet in question...

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("H7:H22")) Is Nothing Then Exit Sub


Application.EnableEvents = False




With Target
    .Offset(, -5) = .Value2
    .Offset(, -4).Resize(1, 2).ClearContents
End With




Application.EnableEvents = True


End Sub

 

[Jonmo1]

Try

Range("C7").Value = Range("H7").Value
Range("D7:E7").ClearContents

 

[erichmiller]

I tried the code and it does not seem to work.

I have read through some of the online information and I have found that since the cells in column H are formula I will need to use worksheet calculate not worksheet change. this is what I tried and it did not work

Private Sub Worksheet_Calculate()
If E7 > 0 Then
Range("H7").Select
Range("H7").Copy
Range("C7").Paste
Range("E7").ClearContents
Range("D7").ClearContents
Else
End If
End Sub

I think this is what you're after. Paste it into the module of the sheet in question...

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("H7:H22")) Is Nothing Then Exit Sub


Application.EnableEvents = False




With Target
    .Offset(, -5) = .Value2
    .Offset(, -4).Resize(1, 2).ClearContents
End With




Application.EnableEvents = True


End Sub