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