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

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

16 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Lisa,

Its good to know that you've now got it up and running.

In relation to your last question, Google Drive/Sheets do not support Excel macros hence you can't directly run Excel macros in Google Sheets.

You can run macros in Google Sheets as long as they're written in Google Apps Script (a JavaScript based language) which is a different language to Excel's VBA (e.g. the code you now have).

If you are using Excel workbooks regularly, I would suggest that you create a folder to store these in and then store the folder in the "cloud" if need be. As with any "cloud" based storage, you'll need to download the required file to your desktop before being able to enable any macros operating in that file. Macros are not supported in cloud storage.

Further more, if any of your Excel workbooks have macros implemented, ensure that the file extension is .xlsm as per the sample in my post #13.

Cheerio,
vcoolio.
2
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
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
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.

Ok. Here is the link: http://ge.tt/3pihIy03 It's bringing the information over from sheet 1 (Orders) but not sorting it by Column D on sheet 2 (Delivery). And, it's putting one row above my frozen header cells when it moved the info over to sheet2. Any help you can give would be great! Thanks again
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Lisa,

I'll have a look at it tomorrow for you.
I'm working away from home at the moment and this will be near impossible to do on my phone.

Cheerio ,
vcoolio.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Lisa,

Having a number of empty columns appears to be what has been causing you some problems.
Following is the code again amended to cover any blank columns. It will also allow for the fact that, in future, you may add more columns to your data set.



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

Application.ScreenUpdating = False

      If IsDate(Target) Then
      Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
      Sheet2.Range("A2", Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp)).EntireRow.Sort Sheet2.[D2], 1
      Sheet2.Columns.WrapText = False
      Sheet2.Columns.AutoFit
      End If

Application.ScreenUpdating = True

End Sub


Following is the link to your file with the amended code implemented. It should be smooth sailing from here on.

http://ge.tt/1maPS013

I hope that this helps.

Cheerio,
vcoolio.

I followed your link to my sample file. Now its not doing anything when I enter data. Nothing at all is being moved to sheet 2. I'm about to give up on the whole thing and just go back to entering it all by hand. Thanks for all your guidance though.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Lisa,

No need to give up.

I've checked the link and all is well. I can use/manipulate your workbook sample without any problems. I would say that you may have forgotten to disable the security warnings on downloading the file.

Hence, click on the link then click "download".
Once downloaded you'll be asked to open the file with a programme: as usual, open it with Excel.
Once open, you'll see a gold coloured banner at the top of the Excel sheet entitled "Protected View". Click on "Enable Editing".
You'll then see another gold coloured banner entitled "Security Warning". Click on "Enable Content".
You will then have bypassed the basic security warnings and you will now be able to work on the sample file.

Further to the above, another reason that nothing appears to be happening could be that you are not making your date entries in Column K the very last entry.
As per my first reply to you on the 15th of January, once a date entry is made in Column K and you click away or press enter or down arrow (or generally tab away from the active cell in Column K), the event code above will execute and the data will be transferred and sorted.
Any date entry in Column K needs to be the very last data entry per row.

Please keep me in touch.

Cheerio,
vcoolio.

Ok, I got it working. Now my last question is, why won't it work when I save it to Google drive and open with Sheets?