Copy rows from Excel to another sheet once a date in a specific field is filledSolved/Closed

Question

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.

vcoolio · Accepted Answer

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.

vcoolio · Answer

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.

lisav041475 · Answer

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

vcoolio · Answer

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.

lisav041475 · Answer

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!

vcoolio · Answer

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.

lisav041475 · Answer

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?

vcoolio · Answer

Hello Lisa,

Yes. That is correct.

Cheerio,
vcoolio.

lisav041475 · Answer

It's not working. It brings the lines over just fine, but won't sort by column the date in column D

vcoolio · Answer

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.

lisa@supgm.com · Answer

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

vcoolio · Answer

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.

vcoolio · Answer

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.

lisa@supgm.com · Answer

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.

vcoolio · Answer

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.

lisa@supgm.com · Answer

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?

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

16 replies

Subscribe To Our Newsletter!