Link to home
Create AccountLog in
Avatar of Student1234
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.PasteValues
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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Try

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.PasteValues
            End With
        End If
enditall:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub
Avatar of Student1234
Student1234

ASKER

Thank you,

 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.
the code pastes the formula in the cell copying. i would like the value of that formula.....
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Works Great thanks
Great~