I have a current spreadsheet that lists our customers, job names, and delivery dates for the materials. I would like to have those lines copy automatically to another sheet once the delivery date is filled in. Is there a code to do this? I am trying to avoid the need to type all of this information on both sheets of the spreadsheet. Any help would be greatly appreciated. Thank you.
Without seeing a sample of your workbook, its difficult to tell exactly what you would like to do.
However, the following code may set you in the right direction:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If IsDate(Target) Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If
End Sub
The code assumes that your target column (the column into which you enter the dates) is Column F (change to suit).
The code also assumes that your data starts in row2 of your source sheet with headings in row1. I've assumed that your destination sheet is Sheet2.
Once transferred to the destination sheet, the relevant row of data from the source sheet is deleted.
The code is a Worksheet_Change event code and needs to be placed into the source worksheet module. To implement this code:-
- Right click on the source sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.
Back in your source sheet, once you enter a date in Column F (or whichever column is your date column) and you click away or press enter or down arrow, the relevant row of data will be transferred to Sheet2 and the row of data from the source sheet will be deleted.
Is there a way to keep the original data and just copy it to Sheet 2? Also, the target column is K. I've included a sample of the sheet for you to see. Thank you for all your help!!
Thank you. I figured it out right after I sent the last reply. I do have one more question. How can I make sheet 2 sort automatically by one of the columns as soon as a line gets copied to it? Or is that not possible? I really appreciate all your help!
This will sort the data in Sheet2 based on the dates in Column K, ascending. Change the "L" above to whichever column is the last column in your data set.
I've just tested the code in a workbook I believe to be set out similar to yours and the code works exactly as it should.
Here is the code again:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("K:K")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If IsDate(Target) Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Sheet2.Range("A2", Sheet2.Range("M" & Sheet2.Rows.Count).End(xlUp)).Sort Sheet2.[D2], 1
End If
End Sub
It covers Columns A-M.
If you think that the code still doesn't work in your actual workbook, then please upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. If your data is sensitive then please use dummy data. Seeing a sample of your workbook will allow us to test in a working sample.
