Archiving Cells in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelBeginner - 27 Jun 2007 17:04 GMT

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.

Reply to this Message

AKphidelt - 27 Jun 2007 17:40 GMT

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

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

Reply to this Message

ExcelBeginner - 27 Jun 2007 18:34 GMT

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!

> You can do this using a worksheet event with a
>
[quoted text clipped - 21 lines]
> > automatically remove that row and archive it elsewhere?
> > Thanks in advance.

Reply to this Message

Peo Sjoblom - 27 Jun 2007 18:41 GMT

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

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

Signature

Regards,

Peo Sjoblom

> Thanks, that's really helpful however I don't understand how to write it
> into
[quoted text clipped - 27 lines]
>> > automatically remove that row and archive it elsewhere?
>> > Thanks in advance.

Reply to this Message

AKphidelt - 27 Jun 2007 23:56 GMT

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

> 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.
[quoted text clipped - 25 lines]
> > > automatically remove that row and archive it elsewhere?
> > > Thanks in advance.

Reply to this Message

Rate this thread: