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

uberbrah

New Member
Joined
Aug 10, 2018
Messages
3
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!

 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hello Uberbrah,

Try the following code placed in the worksheet module:-


Code:
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Re: Post #5

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

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top