macro for moving row contents depending on several conditions

Hi all,

I have two worksheets. one is for ongoing activity. the other is to archive items that have been dealt with. I have a column entitled "outcomes" which has several possible entries that users select from s drop down menu.

Periodically - maybe once per day, or once per week i need to archive entries from the ongoing worksheet.

what i would like to do is have a macro that:

a) selects all rows from the "ongoing" worksheet that have Outcome = x, y or z

b) move those rows to the Archive worksheet (in the same workbook)


any help would be greatly appreciated

Hi

Here's a start

Please Login or Register  to view this content.

I've nominated column F as being the outcome column (you don't specify) and sheet2 as the archive worksheet.

rylo

Thanks a lot for the help.

sorry i wasnt specific enough.

The macro does some odd things, though it could be my mistake.

1 -when i run it, it creates a new row in the archive worksheet but the entries are all the letter "d"

2- there is only one new row, while in the test worksheet i had 2 rows containing conditions that should have seen them moved.

3- the original rows still exist. I would like to move them completely to the archive worksheet.

I have a number of drop down menu's and some conditional formatting on some of the cells. In addition i use a userform to input the data. I dont know if this effects the macro.

On a further note. I would like to have the button for the macro on a third worksheet, (along with the macro that runs the userform and instructions for users.) Am i correct in thinking the code you entered would need a line defining the source worsksheet added?

excuse my minimal macro experience and thanks again for all the help

raf

raf

See if the attachment is doing what you require.

rylo

Yes, something like this is what i am after, but:

when I run that macro it appears to be selecting the correct lines, however, if you do it with new information, for example by adding new rows, it seems to brake down.

I added some text to other cells in each row and when the macro was run it would not copy the information across correctly.

I appeared to only copy the information in the non-F columns and then paste over information already in the destination worksheet.

thanks again for your continued afforts!

HI

It would. You haven't given any real detail on your structure, and without any detail it is hard to work out what to base things on. How about you put up an example file that shows your structure. Don't include any sensitive data, but make sure the structure and data types are relevant.

If you want to include some scenarios (ie do this extract, then add this data, then reextract), then great - gives us something to work on. Make sure that you detail exactly what should appear where when things have finished.

rylo

hi again

sorry about the lack of detail.

I cannot easily upload from this computer so i will try to give a clearer description of what i am doing. If it's an issue, i can upload something later when i get home.

summary.

I am making a spreadsheet to collect data on incoming messages to our service. It consists or personal information laid out in a row

eg:

time of call / name of caller/message/who the message is for/outcome/ etc

as we accumulate a large number of calls i wanted to be able to filter out data sets at the end of each day and put the completed calls in an archive.

I use a userform to input the data and timestamp the entries. I also have conditionally formated the spreadsheet to so users can easily follow what has happened to incomming enquiries.

as a sidenote - the spreadsheet is being shared across a local network consisting of Macs only. (just in case that is an issue) Though this is all working pretty well for the most part. Many people and volunteers will be using the spreadsheet to record messages so i need to try and keep it pretty easy to use.

I realise there are probably better/easier/more eleagent software solutions to what i am trying to do but i was asked to do this in excel so am doing my best.

and yet again thanks for all the effort, you have been awsome

raf