Archiving Cells in Excel

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.

0
6/27/2007 4:04:04 PM
excel 39879 articles. 2 followers. Follow

4 Replies
453 Views

Similar Articles

[PageSpeed] 29

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
 
"ExcelBeginner" wrote:

> 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.
> 
0
AKphidelt (33)
6/27/2007 4:40:01 PM
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!


"AKphidelt" wrote:

> 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
>  
> "ExcelBeginner" wrote:
> 
> > 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.
> > 
0
6/27/2007 5:34:00 PM
It's code, you don't put it in the cell


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



-- 
Regards,

Peo Sjoblom

"ExcelBeginner" <ExcelBeginner@discussions.microsoft.com> wrote in message 
news:38737977-2466-4A9D-B261-DC828C736327@microsoft.com...
> 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!
>
>
> "AKphidelt" wrote:
>
>> 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
>>
>> "ExcelBeginner" wrote:
>>
>> > 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.
>> > 


0
terre081 (3244)
6/27/2007 5:41:48 PM
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

"ExcelBeginner" wrote:

> 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!
> 
> 
> "AKphidelt" wrote:
> 
> > 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
> >  
> > "ExcelBeginner" wrote:
> > 
> > > 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.
> > > 
0
AKphidelt (33)
6/27/2007 10:56:01 PM
Reply:

Similar Artilces:

move to archived folders works, archiving to same archived folders does not work
We are using Outlook 2000 with Exchange 2000. Most of our users work on Terminal Server. Their pst-files are stored on a directory on the file and print server. Some of these users can move mails from their mailbox to their archived folders, but cannot archive to it. They get the message that the pst file cannot be opened. However at that moment they can still browse in their archived folders, move mail to it and so on. For one user I created a new pst file, but the problem staid the same. Does anyone have experience with this? ...

operation with excel
Hi, I don't undestand why I don't have the same result with these two formulas : =IF(1.02-1.01=0.01,"ok","not ok") => the result is : ok =IF(2.02-2.01=0.01,"ok","not ok") => the result is : not ok Thanks GJ Hi see: http://www.cpearson.com/excel/rounding.htm So use =IF(ROUND(2.02-2.01,2)=0.01,"ok","not ok") -- Regards Frank Kabel Frankfurt, Germany "GJ" <gjeandot@wanadoo.fr> schrieb im Newsbeitrag news:OnanOf92EHA.3452@TK2MSFTNGP14.phx.gbl... > Hi, > I don't undestand why I don't h...

Workbook_Open CommandBar corrupt excel files
Hi, I have 2 macros that create and delete a custom command bar at the opening and before close. A) When I save the file and then click on "X" without saving, I can reopen the file. B)But when I save the file, click on "X" and click on save, I can't reopen the file (Excel ask to recover the file). Does anyone have an idea? Thank you! Alex Private Sub Workbook_Open() Call CreateCommandBar End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteCommandBar End Sub -- Alex St-Pierre I think it's coincidence. Try creatin...

How can I enter a cell in excel with hotkey or combination off key
F2 ?? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "theo passier" <theo passier@discussions.microsoft.com> wrote in message news:8DA5E96E-8A5B-43F2-A15B-210C14A94E0F@microsoft.com... > ...

Leaving a total sum cell blank until other cells are filled
Hi there, some help needed please for probably a simple problem. Lets say I have a value in cell A1. A2 is blank for now and the total of the SUM will be in A3. How can i get it so that A3 is blank until A2 is filled in. Any would be appreciated Try this in A3: =3DIF(OR(A1=3D"",A2=3D""),"",A1+A2) That checks for both A1 and A2 being empty, and if either of them are then the formula will return a blank. If you only want to test for A2, then you can do this: =3DIF(A2=3D"","",A1+A2) No need for SUM. Hope this helps. ...

Text Entered in A Cell Sets Another Cell's Value?
When a user enters text (any text) into cell B5, I want cell A5 to b set to "Other Job Specialty". Cell A5 contains a drop down list of jobs (approximately 30), with th last option of "Other Job Specialty", which I would like to have appea as the cell value if the user starts typing in their own job specialt area in cell B5. Attached is the worksheet, any insight will be most appreciated. Thanx - DarnPi Attachment filename: nurse_jobs.xls Download attachment: http://www.excelforum.com/attachment.php?postid=65088 -- Message posted from http:...

Excel dafault color palette
How can I PERMANENTLY change the colors in the default color palette in my Excel workbooks? Currently, when I change a color in a workbook, the color reverts back to the old color when I close and re-open the workbook, even when I save the file before closing. Hi! I don't seem to have this problem (XL2003, but I'm sure 97 and 200 were the same) I open a new workbook. Change a colour (Tool > Options > Color etc) I save the workbook. I reload the same workbook and it still has the changed colour. Moreover, if I now open another workbook and Tools> Options> Color an ...

How do I make the Y axis in an excel chart the category axis?
I would like to create a scatter chart where the x axis is the value axis and the y axis is the category axis, and I can't seem to figure out how to switch the two. Jon's site will tell you how to do so . . . http://www.peltiertech.com/Excel/Charts/axes.html#SwitchXY ---- Regards, John Mansfield http://www.pdbook.com "megnadoodle" wrote: > I would like to create a scatter chart where the x axis is the value axis and > the y axis is the category axis, and I can't seem to figure out how to switch > the two. Hi, If you want the Y axis to be a category...

Formula in Excel 07 to get the formula used in one cell in another cell, showing VALUES, not cells references
Hi, I'm gonna illustrate this by an example as I think it is the easiest way to explain what I need: A1 = 10 A2 = 5 A3 = 2 because A3 = A1/A2 Now, what I want is to be able to in cell B3 to write some sort of formula e.g. =GetFormula(A3) that will give me: =10/5 and NOT =A1/A2 , NOR 2 Does anyone know what I can do? I don't know how to work with VBA or anything, but I know how to copy- paste if you know the codes. I previously got the following answer: ------------------------- Here is a custom function that will do what you want. To install the custom function: Alt+F11 to ope...

Lists, Excel 2003, automatic subtotals
I have created a List using Excel 2003. I have sorted the list on the selected field. Instructions say to select a cell outside the list and select Data, Subtotal. However, the Subtotal is grayed. Therefore, the Subtotal dialog box is not available. I have done this in the past with Excel 2002; then one had to be in the list for the Subtotal dialog box to be available. However, if I go inside the List, it doesn't work either. Any help would be appreciated. Hi Suomi, > I have created a List using Excel 2003. I have sorted the > list on the selected field. Ins...

Counting consecutive cells
I have a spreadsheet of hours worked per day, for employees. I need to have an easy way (a function would be great), that can tell me if any worker has worked more than 6 days in a row. If an employee only works Mon - Fri, them there would be a value of 0. If an employee worked 5 days, Sat and Sun, and then another 5 days, the function would return 12 (the count of consecutive days worked, greater than 6 consecutive days). The reason for this is to keep a check on worker fatigue, and compliance with maximum working hours. Thanks for any help OM hi, can you describe or show the data dispositi...

Archive
Hi, I'm trying to archive some calendar items and have found the archive settings for the folder. When I run the archive process the items are not moved from the calendar and the folder size is not reduced. I'd appreciate it if someone could tell me how to set this up so that items prior to a point in time can be moved off so that the size of my calendar folder doesn't reach the size limit of my over zealous exchange administrator. Cheers, Darren. Check the Modified date on the items that aren't being archived as you think they should. That's the date Outlook uses to...

Archival Data -- Managing over Time
I am developing a set of data that I will do some research on, and others as well. My plan is to keep the source data in one workbook in a very simple format, and not do any calculations or charts in that workbook. I'll do my charts in other workbooks, and will send the source data workbook to others when they request. The idea is to reduce corruption and accidental modifications to the source data over time. Is this a good plan? Any other comments about how to protect the accuracy and stability of the source data over time? I understand I can essentially make the source file read...

How do I add to one cell and auto subtract from another in Excel?
I want to track vacation days: taken and left to take. How do I link two cells so when I add to one the other automatically subtracts the same value? "Al" <Al@discussions.microsoft.com> wrote in message news:207CACCA-F715-4B6E-A242-3C95A6F0B42D@microsoft.com... >I want to track vacation days: taken and left to take. How do I link two > cells so when I add to one the other automatically subtracts the same > value? Let's say you start with 25 days leave available and you want to put the number of days taken into A1. In the cell where you want the number of ...

Displaying locked cells
I know that you can tab to unlocked cells, but when you have been designing a spreadsheet, is there a way of displaying which cells are locked (protected) and which are not? >-----Original Message----- >Mike > >I like to use a conditional format for this: > >In cell A1, call up Format / Conditional Format. In the drop down box select "Formula is" and then enter the following. > >=CELL("Protect",A1)=1 > >Select a suitable cell background colour (or font colour, or whatever) for the condition then OK. Copy your format to the rest of your...

deleting empty folders after archive
IS there a way to delete folders which are left empty after you perform an archive? I have approx 300 or so folders that are empty since my archive application completed but they still show up and there seems to be no way except one at a time to delete them. There should be a method to state if folder size = 0 then delete folder. Shane ...

If the cell is empty, copy the value from a specific cell.
Hi there, How do I write a macro that will check the cells to see if they are empty. If the cell J3 is empty, if it will copy the content from J2 onto J3. Do the same for J4, J5... J8. Thanks, Aline If IsEmpty(Range("J3")) Then Range("J2").Copy Range("J3") -- Regards Dave Hawley www.ozgrid.com "Aline" <Aline@discussions.microsoft.com> wrote in message news:21D22A6A-418C-4769-89C7-B74CE36956A6@microsoft.com... > Hi there, > > How do I write a macro that will check the cells to see if they are empty. > I...

Export account to Excel
I want to export my account transactions to Excel. From the help file, it looks like you can only export reports to Excel, but what I am really interested is to have line by line transactions in a spreadsheet. So, is there a way to create a report that will look exactly the way the account transaction page look like (I dont know how to do that), or is there any way we can export a file directly from the transaction page. Thank you, Vanessa See http://www.bollar.org/msmoney/#Q9 for the other way. It can't do everything, but what MoneyLink can do, it does quite well. "Vaness...

Excel refreshing of data. Not happening. Hoiw Bizzaar
Hi There. So each time I change a cell the auto sum does not add up the change just keeps the original summed figure there....until you click on Save. The Sumed total then displays correctly. Any ideas on how i get excel to perform normally again. The file is about 1Mb Tools>options>calculations set it to automatic -- Regards, Peo Sjoblom "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:9C6A7649-CEDE-4A60-B597-F264E8612C12@microsoft.com... > Hi There. > So each time I change a cell the auto sum does not add up the change just > k...

retreive deleted excell work sheets?
can I retrieve deleted excell work sheets? Save the current version as a new name. Open the old version (you still have that, right)?? Copy the worksheek from the old version to the new version. If you had formulas in other sheets that pointed to the "deleted" sheet, you'll have to fix those. ====== If you haven't saved after you've deleted the sheet: Lots of times, it's just easier to close without saving (losing all the changes you've made since your last save). Then reopen a good copy and make all those changes again. done it wrong wrote: > > can ...

Archiving pages
Hi All, The pages in one of the our publishing site are around 1500. So it?s showing effect on search crawling index and sometimes throwing an error like System out of memory (usually in full crawl). Our ram size is 4 gb for index server. Is there any method for archive the pages in page document library. And the important thing is the data must be handy, so the archival process should accommodate ability to retrieve the archived content quickly. pls suggest me. Thanks in Advance sukumar.k Submitted via EggHeadCafe - Software Developer Portal of Choice Consuming WebServices...

add password in excel file
How to add password in MS Excel file. That means the user open project1.xls, it will pop up a password dialog, and user needs to enter the correct password before accessing the file. please advise! thanks! Matt File>Save As>Tools>General Options. Set your Password to Open here. Or on Worksheet Menu go to Tools>Options>Security and set Password to Open. Gord Dibben Excel MVP On Sun, 7 Mar 2004 09:26:49 -0800, "Matt" <mattloude@hotmail.com> wrote: >How to add password in MS Excel file. That means the user open project1.xls, >it will pop up a passwor...

Cell formulas
Hi, I have a request that I can't seem to perform. I have two columns. COL1 contains user inputed values. COL2 has a formula. Basically what I want to do is: In COL2, I have the IF statement. If the statement = true, then I want a "happy face" to show up in the cell. If not, make the cell blank. Is this possible? TIA, Eric Hi see your post in excel.worksheet.functions P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany "Eric D." <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:1397501c44401$80f9d810$a501280a@phx.g...

how do I enable "import text file" excel 2002?
When importing text files into an Excel worksheet I previously went to "data" > "import external data" > "import text file" With Excel 2002 my choices lead me to a"select data source" window which is much more cumbersome. Does the "import text file" still exist in excel 2002? I pretty much use: file|open and copy that imported data to where I want it. But you can add an icon to your favorite toolbar to skip a step or two. tools|customize|commands tab|Data category look for "import data" and drag it to where you want. (n...

Formula Needed for Corresponding Blank Cell
Hi All, Well that didn't work: =IF($B$7:$B$689<>"",IF($W$7:$X$689="","SOMETHING IS MISSING",""),"") And neither did this: =IF($B$7:$B$689<>"",IF(OR($W$7:$W$689=""),OR($X$7:$X$689=""),OR($Y$7:$Y $689="")),"SOMETHING IS MISSING",""),"") No laughing. I'm trying. And I did use Ctrl-Shift-Enter. It's supposed to catch blank cells in columns W, X, or Y if column B in the same row isn't blank. Column B is a date and W, X and Y are ...