Copy rows from Excel to another sheet once a date in a specific field is filled

Report
Posts
5
Registration date
Tuesday January 14, 2020
Status
Member
Last seen
January 23, 2020
-
vcoolio
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
-
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.

9 replies

Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
Hello Lisa,

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.

I hope that this helps.

Cheerio,
vcoolio.
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5394 users have said thank you to us this month

Posts
5
Registration date
Tuesday January 14, 2020
Status
Member
Last seen
January 23, 2020

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!!
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
Hello Lisa,

To keep the original data just remove line 8 from the code above.

If the target column is K then, in line 3 of the code above, change "F:F" to "K:K".

I hope that this helps.

Cheerio,
vcoolio.
Posts
5
Registration date
Tuesday January 14, 2020
Status
Member
Last seen
January 23, 2020

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!
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
Hello Lisa,

In the code in my post #1, change line 8 to:-

Sheet2.Range("A2", Sheet2.Range("L" & Sheet2.Rows.Count).End(xlUp)).Sort Sheet2.[K2], 1


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 hope that this helps.

Cheerio,
vcoolio.
Posts
5
Registration date
Tuesday January 14, 2020
Status
Member
Last seen
January 23, 2020

So if I want Sheet 2 to sort by column D, would I just change the K2 at the end of line 8 to D2?
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
Hello Lisa,

Yes. That is correct.

Cheerio,
vcoolio.
Posts
5
Registration date
Tuesday January 14, 2020
Status
Member
Last seen
January 23, 2020

It's not working. It brings the lines over just fine, but won't sort by column the date in column D
Posts
1256
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 24, 2020
208
Hello Lisa,

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.

Cheerio,
vcoolio.