Automatically transferring a row to an archive once a specific cell becomes filled

[uberbrah]

Hello,

I am currently creating a spreadsheet that will be used forclient data-entry into excel. I am fairly new to excel and was wondering ifsomeone would be able to help me with developing a macro that will automaticallyarchive rows into a separate worksheet after they have become “completed” at acertain point. For instance, the last two columns in the row are titled “dateservice began” and “date service ended”, is it possible to have the entire row transferredas soon as the “date service began” cell has been filled in? I browsed theforum, but couldn’t seem to find an example where the triggering cell’s contentwas more broad than a specific value. Thank you very much for any help!

 

[vcoolio]

Hello Uberbrah,

Try the following code placed in the worksheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet: Set ws = Sheets("Archive")

If Intersect(Target, Columns(10)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If Target.Value <> "" Then
Target.EntireRow.Copy ws.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If

End Sub

The code assumes that the "Date Service Began" column is Column J (10). Once you place a value in a cell of the relevant row and then click away (or press enter or down arrow) the entire row will be transferred to the Archive sheet. The row of data is then deleted from the main sheet.

To implement the code:-

- Right click on your main sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

P.S. Please test the code in a copy of your workbook first.

 

[My Aswer Is This]

It would be easier if you were able to tell me what column is the last column.
For example say column 12 or column 14

And you said this:

will automaticallyarchive rows into a separate worksheet​

What is the name of this separate worksheet?

 

[Smitty]

Welcome to the Board!

It's generally not a good idea to separate your data like that, because you then lose the ability to easily summarize it. I'd use some code to hide the rows that meet your criteria, or even just use AutoFilter. That way you can use PivotTables to summarize your data.

 

[uberbrah]

Thank you all for your advice and responses.

vcoolio- Thank you so much for this code. It seems to be working perfectly! I definitely owe you one.

My Answer Is This- Thanks for asking for more details. I was able to modify vcoolio's code to meet my input, but thank you for your interest and offer.

Smitty- Thank you for the advice and consideration for how the data will be used. I'm very new to excel, so appreciate all of the advice that I can get.


I do have one more request, if that is alright. Is there away to make it so that the rows that have a blank entry for the “date treatmentbegan” column appear in red? So far I can only get it to highlight the specificcell, as opposed to having the entire row change color based on the entry, orlack of an entry in this case, in the cell for the “date treatment began column”,which is column N. Once again, any adviceis very much appreciated.

 

[Smitty]

I do have one more request, if that is alright. Is there away to make it so that the rows that have a blank entry for the “date treatmentbegan” column appear in red? So far I can only get it to highlight the specificcell, as opposed to having the entire row change color based on the entry, orlack of an entry in this case, in the cell for the “date treatment began column”,which is column N.

Select the entire range you want highlighted, then in Conditional Formatting, you can use the Formula option > =$N2="" > format as desired. Note the $ on N, which will tell Excel to only look at column N, but still apply the format to the entire row.

 

[uberbrah]

Select the entire range you want highlighted, then in Conditional Formatting, you can use the Formula option > =$N2="" > format as desired. Note the $ on N, which will tell Excel to only look at column N, but still apply the format to the entire row.

Thank you, Smitty. However, for some reason, when I try this there are some rows withempty values in column N that still don’t receive colour, and also some rowsthat do have values and also still receive colour. Any idea what im doingwrong? Thanks again

 

[Smitty]

If you want, you can post the workbook on a file sharing site such as OneDrive, then post a link back here and someone can take a look. Just be sure to remove any sensitive personal information first.

 

[vcoolio]

Re: Post #5

You're welcome Uberbrah. I'm glad that we were able to help.

Cheerio,
vcoolio.