VBA Allocation Tool Help

[yidjacko]

Hi All,

I'm in the middle of creating an allocation tool in which on the "Home Page" has a cell ("L12") for the user to type in how many jobs they want to allocate based on what is available. On another sheet ("Latest Jobs") i have the raw data (A:P) and one row = one job. The first colulmn "Allocated" works out if the job has already been allocated ("Yes" or "No") by looking at another sheet ("Allocation") which is filled with all previous allocated jobs.

My plan is to allocate jobs (rows) based on what the value in "L12" is on the "Home Page" and if the "Allocated" value is "No" in column "Allocated" on the "Latest Jobs" sheet. For example, if "L12" value is 10 then the Macro would copy the first 10 rows with "Allocated" value as "No" on the "Latest Jobs" sheet and paste them after the last row on the "Allocation" sheet.

I would appreciate any help you can offer.

Thanks

[TMS]

Please see the attached sample workbook.

Please Login or Register  to view this content.

Regards, TMS

[yidjacko]

Thank you that's great, however now what i have been trying to do is to not copy the "Allocated" (A) column on the "Latest Jobs" sheet. So i only want to copy B:P on the "Latest Jobs" sheet and then paste that after the last row on the "Allocation" sheet but starting from column C. This is because i have an extra 2 columns on that sheet.

I've tried but with no success so far. Any idea?

Thanks

[TMS]

OK, harder than I thought, but try this:

Please Login or Register  to view this content.

Regards, TMS

[yidjacko]

That is perfect, thank you - so above my level unfortunately!!

One more thing if possible..... I have another cell on the home screen ("L11") which contains "Agent Name" and i want to add this into each of the rows copied over from "Latest Jobs" to "Allocation" and placed in column "B" of each, if that makes sense?

[TMS]

This is what we used to call "scope creep".

OK, last time.

Please Login or Register  to view this content.

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[yidjacko]

Sorry i knew the requirements from the start but wanted to see if i could work the rest out myself (I couldn't). Thanks again!