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

Date	(blank)	(blank)	Agent	Deposit	Withdraw
28AUG19			Test1		10.00
28AUG19			Test2	25.00

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...

Date	In	Out	Balance	Remarks
28AUG19		25.00		Sent To Test 2

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)

Date	In	Out	Balance	Remarks
28AUG19	10.00			Sent To Test 1

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.