Copy or Move row on condition to different and/or multiple sheets

[Puk]

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

 

[Luke M]

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.

 

[Puk]

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 :/