Student1234
asked on
Trouble with a bit of VBA script in excel
Private Sub Worksheet_Change(ByVal Target As Excel.range)
'when entering data in a cell in Col M
On Error GoTo enditall
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Target.Cells.Column = 13 Then
With Target
.Offset(0, -3).Select
'moves to col J in same row
Selection.Copy
.Offset(0, 31).Select
'moves to col AR
.Value = ActiveCell.PasteSpecial.Pa steValues
End With
End If
enditall:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
'Need something to return to the original selected cell in the row to col m and down one giving the appears of a normal entry.
it does everything i want but go home.
I pieced this together attempting to have a cell copied and pasted as a value was enter in another. such as,
enter value in column M cell. copy COL J cell. Paste Value in Col AR Cell and return to Column M plus a row. given the usual placement after entering a value. but do not know enough to know enough a little about it.
'when entering data in a cell in Col M
On Error GoTo enditall
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Target.Cells.Column = 13 Then
With Target
.Offset(0, -3).Select
'moves to col J in same row
Selection.Copy
.Offset(0, 31).Select
'moves to col AR
.Value = ActiveCell.PasteSpecial.Pa
End With
End If
enditall:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
'Need something to return to the original selected cell in the row to col m and down one giving the appears of a normal entry.
it does everything i want but go home.
I pieced this together attempting to have a cell copied and pasted as a value was enter in another. such as,
enter value in column M cell. copy COL J cell. Paste Value in Col AR Cell and return to Column M plus a row. given the usual placement after entering a value. but do not know enough to know enough a little about it.
ASKER
Thank you,
could you explain generally why an .offset .select would not work after the paste.value?
could you explain generally why an .offset .select would not work after the paste.value?
I don't know why. It is probably designed that way.
ASKER
the code pastes the formula in the cell copying. i would like the value of that formula.....
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Works Great thanks
ASKER
Great~
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Target.Cells.Column = 13 Then
With Target
.Offset(0, -3).Copy .Offset(0, 31)
.Value = ActiveCell.PasteSpecial.Pa
End With
End If
enditall:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub