Copy a value of a cell into a certain cell based on value entered...

[MarcSwift]

Good evening all,

The title sounds confusing so please let me try to explain.

What I am trying to achieve is...

When I type in a certain number (lets say "123") into one of those blank cells below into a sheet named "April", I would like the date above "123" to be copied and pasted into another sheet named "Annual Leave Taken" but only next to the next available cell in that row which has "123" it in.

Sheet "April"

Sheet "Annual leave taken"

Any help with this would be absolutely brilliant as I have been trying all sorts of codes and using userforms to input the data but can't for the life of me work it out.

Many thanks for your time.

Marc

 

[tonyyy]

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastCol1 As Long, LastCol2 As Long
Dim n As Long

Set ws1 = Sheets("April")
Set ws2 = Sheets("Annual leave taken")
LastRow1 = Cells(Rows.Count, 1).End(xlUp).Row
LastCol1 = Cells(2, Columns.Count).End(xlToLeft).Column

If Not Intersect(Target, Range(Cells(4, 2), Cells(LastRow1, LastCol1))) Is Nothing And Target <> "" Then
    n = Application.Match(Target, ws2.Columns(1), 0)
    LastCol2 = ws2.Cells(n, Columns.Count).End(xlToLeft).Column
    ws2.Cells(n, LastCol2 + 1) = ws1.Cells(3, Target.Column)
End If

End Sub

The code should be pasted into the April sheet module, not a standard module.

Please note, the sheet "Annual Leave Taken" is first-letter capitalized in your description yet first-word capitalized on your sheet picture; please make sure the actual sheet name matches that in the code.

Also note, deleting a number from the April sheet will not have any effect on the Annual leave taken sheet.

Cheers,

Tony