Archiving Cells in Excel

[Guest]

Hi:
I was wondering if there's a way to archive a whole row in excel. For
example, if a cell in the row is selected as 'complete' can excel
automatically remove that row and archive it elsewhere?
Thanks in advance.

 

[Guest]

You can do this using a worksheet event with a

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "Complete" Then
ActiveCell.EntireRow.Copy
Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1,
0).PasteSpecial (xlPasteValues)
ActiveCell.EntireRow.Delete
End If
End Sub

I didn't have much time to work on this but this should get you started.
This doesn't make the change automatically when you type "Complete". If you
type complete, then get out of the cell, then click the cell... then the
event will occur. And make sure you have headings in sheet2 that match sheet1
headings so that the rows stay consistent.

lmk

 

[Guest]

Thanks, that's really helpful however I don't understand how to write it into
the cell. If you could give a specific example that'd be great.
Thanks so much!

 

[Peo Sjoblom]

It's code, you don't put it in the cell


http://www.mvps.org/dmcritchie/excel/event.htm

 

[Guest]

Follow these directions...

Go To

Tools--> Macro--> Visual Basic Editor

On the left you'll see a window called the project explorer... double click
on Sheet1 or whatever sheet has the data...

Then in the upper left you'll see a drop down box that says "General".
Change that to "Worksheet"

Then copy and paste the formula from my previous post word for word.

Keep in mind that you should play around with it on a test document first,
because you can not undo a macro. But once you put it in click on any cell
that has the word "Complete" in it and watch it work. Also make sure Column A
always has data in it, because the macro selects the first empty row in
Sheet2. So if column A doesn't have data but column B does, the next time you
run it, it will paste over the previous data. Let me know