Can I simplify a VB code??????

H

hoyos

Is there an easier way of writing this code?

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

Sheets("Sheet2").Range("j187").Copy
Sheets("Sheet2").Range("j188").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("Sheet2").Range("j192").Copy
Sheets("Sheet2").Range("j193").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("Sheet2").Range("k187").Copy
Sheets("Sheet2").Range("k188").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("Sheet2").Range("l187").Copy
Sheets("Sheet2").Range("l188").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False

End Sub
 
L

Luke M

'Rather than doing a copy & paste, you can tell VB directly
'that one cell equals another


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

With Sheets("Sheet2")
..Range("J188") = .Range("J187").Value
..Range("J193") = .Range("J192").Value
..Range("K188") = .Range("K187").Value
..Range("L188") = .Range("L187").Value
End With

End Sub
 
H

hoyos

Thanks Luke I shall try that. I'll let you know!

Luke M said:
'Rather than doing a copy & paste, you can tell VB directly
'that one cell equals another


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

With Sheets("Sheet2")
.Range("J188") = .Range("J187").Value
.Range("J193") = .Range("J192").Value
.Range("K188") = .Range("K187").Value
.Range("L188") = .Range("L187").Value
End With

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
 
H

hoyos

Thanks Luke it works.
The problem I am getting now, when changing a value in one of the textboxes,
takes along time to run through the calculations.
Any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top