Copy cell contents to another sheet at a particular cell address

[sfshah]

I need a VB code to copy data manually entered in Purchase Sheet in column E to Sale Sheet in Column R in Row number as appearing in column A of Purchase Sheet.


The row numbers in column A change as different invoice numbers are fed in Purchase Sheet Cell F5. For each invoice different amount is manually entered in Purchase sheet range E9:E22.


On using the code (Worksheet - SelectionChange)


Private Sub Worksheet_selectionChange(ByVal Target As Excel.Range)


If Selection.Value = "SUBMIT" Then
Sheets("SALE").Range(Selection.Offset(0, -5).Value).Value = Selection.Offset(0, -1).Value
Range("A1").Select
End If


End Sub


I get the results properly, but each time a cell is selected in Purchase sheet, a Run Time Error message box pops up, making it very difficult to work. How can this be avoided?