Data validation List Change event using VBA and Copy corresponding single cell data and paste it in another sheet.

Question

Hello Everyone

Please help me to solve my problem.

Here in this image , whenever i change the status "Unpaid" or "Paid" in Stuinfo sheet........

I want to select the selected corresponding row cell i.e. (B11) (Selected index) want to copy this cell value into sheet cashbook last row b column.

Please help me or VBA code.... to perform this operation

i need this as soon as possible.....

Its very urgent

Answer 1

Hi satinderverma

You may try this code

''''**********************************************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

Dim statusRange As Range

'''The dynamic status range from cell R4 down to the end of your data

Set statusRange = Range(Cells(4, "R"), Cells(Rows.Count, "R").End(xlUp))

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(Target, statusRange) Is Nothing Then

    Cells(Target.Row, "B").Copy Sheets("cashbook").Cells(Rows.Count, "B").End(xlUp).Offset(1) 

End If

End Sub

''''''******************************************************************

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany