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

 

[SupremeDr]

Are the last populated cells always at the bottom of your table/data?

if so you can use offset to grab the cell at the bottom.

Dim table1 as range, rowcount as Long
Set table1 = range("A1").Currentregion
rowcount = table.rows.count
Dim FooterRow as range
Set FooterRow = table1.rows(1).offset(rowcount - 1) 'remove the -1 if needed.
FooterRow.copy

you can use the same idea to set the bottom row of Sheet2 and paste it in

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in either column D or E and press the RETURN key.

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

 

[redspanna]

Thanks for this - like this option, but can it be tweaked?

when the RETURN key for column 5 or 6 is selected I'd like following to happen...

value entered into column 5 and RETURN key is selected, this value is copied into column C of sheet2 along with value of column 4 copied into column E
value entered into column 6 and RETURN key is selected, this value is copied into column B of sheet2 along with value of column 4 copied into column E

appreciate your help and thanks again

 

[redspanna]

.

 

[mumps]

Are you now using columns E and F (5 and 6) instead of columns D and E (4 and 5)?

 

[redspanna]

Are you now using columns E and F (5 and 6) instead of columns D and E (4 and 5)?

yes....E and F but also want the value of D copied across as explained too if possible


thanks again

 

[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) = 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) = Target.Offset(0, -2)
    End Select
End Sub

 

[redspanna]

Perfect - thanks a lot for your help

 

[mumps]

You are very welcome.