Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Copy or Move row on condition to different and/or multiple sheets
Good afternoon.
I'm new to the world of programming in excel but have programmed elsewhere previously. What i am trying to do is to have a todo list setup, ive searched long and hard and can't find anything that quite fits my bill that i can replicate and alter to work so am asking for some assistance. I have a workbook with six sheets, All Jobs, Evaluation, Authority, In Progress, Completed and a helper sheet. In all jobs i have 9 columns A-I (atm A being spare) that have information that i need to show. In column 'I' i have a dropdown box using the helper sheet for picking 1 of the 4 conditions that the job is in process of (ie. evaluation, authority, progress, completed). I'm trying to create a setup so all the jobs are seen on the first sheet, and when changed to a different state (using the dropdown in I) they are copied onto that the relevant "state" sheet. What is needed to be copied is the entire row of information for that job. Finally only once they are changed to "finished", they are removed from the 'All Jobs' to the 'Completed' sheet so they can be deleted when no longer needed for reference. Each sheet's data starts on row 6 to allow for a header on top of each, allowing a nicer presentation should it be printed. Would really appreciate some help with this and hope that my explanation does justice for my purpose! Thanks :) |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Copy or Move row on condition to different and/or multiple sheets
When exactly do you need the information moved? How about when you open the
workbook? In which case, open the VBA editor (alt+F11) and go to the ThisWorkbook sheet. Use Private Sub Workbook_Open() as your name. For the actual program, the simplest (and easiest to explain) would be to record (via Tools-macro) these steps and then clean them up into your workbook open sub. How I'd do it, (start of macro) Clear data from your condition sheets (except 'completed'). apply autofilter to main sheet. Select on of your conditions. Copy all the significant data to the condition sheet. Select next condition, copy, etc. Fot completed, copy data, got to completed sheet, select column A, Ctrl+down, (then, during your cleanup, use this line activecell.offset(-1,0).select to get to the next blank line) then copy the data. (end macro) Again, from the editor there's a bunch you can do to cleanup/make more efficient, but you should be able to figure it out. Sorry if this doesn't go into much detail, but as you said you had some experience, I didn't want to bog you down with details you already could figure out. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Puk" wrote: Good afternoon. I'm new to the world of programming in excel but have programmed elsewhere previously. What i am trying to do is to have a todo list setup, ive searched long and hard and can't find anything that quite fits my bill that i can replicate and alter to work so am asking for some assistance. I have a workbook with six sheets, All Jobs, Evaluation, Authority, In Progress, Completed and a helper sheet. In all jobs i have 9 columns A-I (atm A being spare) that have information that i need to show. In column 'I' i have a dropdown box using the helper sheet for picking 1 of the 4 conditions that the job is in process of (ie. evaluation, authority, progress, completed). I'm trying to create a setup so all the jobs are seen on the first sheet, and when changed to a different state (using the dropdown in I) they are copied onto that the relevant "state" sheet. What is needed to be copied is the entire row of information for that job. Finally only once they are changed to "finished", they are removed from the 'All Jobs' to the 'Completed' sheet so they can be deleted when no longer needed for reference. Each sheet's data starts on row 6 to allow for a header on top of each, allowing a nicer presentation should it be printed. Would really appreciate some help with this and hope that my explanation does justice for my purpose! Thanks :) |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Copy or Move row on condition to different and/or multiple she
Luke, i appreciate your reply. I've had a good fiddle with it, but for some
reason when i run the macro all i get is the same cells being copied, one below the other, each time as the document is opened. So it repeats itself instead of just moving a cell once. When you resave the document and reopen it, it takes the one from alljobs thats still there and then puts it below the its replica from the previous time it ran. I guess i need some sort of if exists condition in the ondocopen statement. I'm a bit lost tbh. I haven't even tried with the completed sheet, kept messing me around. Wish i'd paid more attention in the vba classes i had at college :/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
Cannot Move or Copy Sheets | Excel Discussion (Misc queries) | |||
How do I copy and move selections of data to other sheets? | Excel Worksheet Functions | |||
How do I copy or cut multiple rows to move to new sheet? | Excel Discussion (Misc queries) | |||
copy/move sheets within workbook | Excel Discussion (Misc queries) |