Code to copy/past from one sheet to another

[redspanna]

Hello all

Can some help be given to automate this simple process...

Copy value from last populated cells in coulmn D and E in sheet 1 and paste into first available cell in column B and C in sheet 2

Thanks in advance

 

[redspanna]

Hi mumps - sorry , working with this last night I've got a possible addition - hope you can help


so this line of the code

Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "E").End(xlUp).Offset(1, 0) = Target.Offset(0, -1)

can it be changed so that the text "Sent to" is added along with the copied value from column 5 ?

For example if column 5 holds the value Mr Excel, then the result for the new code would be


Sent to Mr Excel


Hope this makes sense, and appreciate any follow up work

 

[mumps]

I'm a little confused. The "copied value" that you mention would be in column 4 instead of column 5. Is this correct?

Target.Offset(0, -1)

and

Target.Offset(0, -2)

both refer to column E which is column 4.

 

[redspanna]

Sorry for confusion, I'll try and explain with a table below.


This is a sample of the sheet where the original data is kept
[table="width: 150, class: grid"]
[tr]
[td]Date[/td]
[td](blank)[/td]
[td](blank)[/td]
[td]Agent[/td]
[td]Deposit[/td]
[td]Withdraw[/td]
[/tr]
[tr]
[td]28AUG19[/td]
[td][/td]
[td][/td]
[td]Test1[/td]
[td][/td]
[td]10.00[/td]
[/tr]
[tr]
[td]28AUG19[/td]
[td][/td]
[td][/td]
[td]Test2[/td]
[td]25.00[/td]
[td][/td]
[/tr]
[/table]

so using you suggested code, when a value in column 5 (deposit column) is added and RETURN key pressed I would like the phrase "Sent to" added along with the content of column 4 (Agent)

so result of this on sheet 2 would look like...


[table="width: 250, class: grid"]
[tr]
[td]Date[/td]
[td]In[/td]
[td]Out[/td]
[td]Balance[/td]
[td]Remarks[/td]

[/tr]
[tr]
[td]28AUG19[/td]
[td][/td]
[td]25.00[/td]
[td][/td]
[td]Sent To Test 2[/td]



[/tr]
[/table]

or using you suggested code, when a value in column 6 (withdraw column) is added and RETURN key pressed I would like the phrase "Sent to" added along with the content of column 4 (Agent)

[table="width: 250, class: grid"]
[tr]
[td]Date[/td]
[td]In[/td]
[td]Out[/td]
[td]Balance[/td]
[td]Remarks[/td]

[/tr]
[tr]
[td]28AUG19[/td]
[td]10.00[/td]
[td][/td]
[td][/td]
[td]Sent To Test 1[/td]



[/tr]
[/table]


the date in column 1 is auto populated so no need to copy this across...


Does that make more clear? - sorry again for confision

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:F")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 5
            Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "C").End(xlUp).Offset(1, 0) = Target
            Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "E").End(xlUp).Offset(1, 0) = "Sent to: " & Target.Offset(0, -1)
        Case Is = 6
            Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "B").End(xlUp).Offset(1, 0) = Target
            Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "E").End(xlUp).Offset(1, 0) = "Sent to: " & Target.Offset(0, -2)
    End Select
End Sub

 

[redspanna]

BOOM !!

Brilliant - thanks so much, appreciate your help !!

 

[mumps]

My pleasure.