Copying cells to new worksheet based on tickmarks?

[adamsad1]

I need a way to copy and paste information from one worksheet to another based on a "tickmark".

Here’s (simplistically) the way my worksheet is set up.

Columns A through D contain information. Columns E through G are “tickmark” columns. I tickmark (with a “1”) rows based on the information in A through D. Sometimes a row is assigned more than one tickmark.

I then need to copy the information in, say, column A, B, and D (skipping C) based into separate worksheets based on their tickmarks. They need to be copied to a specific location within those worksheets—so, for example, if there’s a tickmark in column E, then A, B and D are copied to A5, B5, and C5 (it can overwrite anything that was previously there) in worksheet "X". The next tickmark in column E gets copied to A6, B6, and C6, and so on down the list. Tickmarks in column F get copied to worksheet "Y" in a similar fashion.

Thanks.

[mojo249]

Hi Adam

Can you attach your spreadsheet? It may be easier to understand what you're trying to do.

Dion

[adamsad1]

Here's a simplified version of what I'm trying to do. The actual sheet I'm using is pretty big--about 1000 tasks, 20+ categories, and 20+ people in charge of those categories (each in charge of 1 to 5 categories). The tasks change every few weeks, so I can't go tagging everything in the entire list and copy/paste all in one shot--it has to be done every week to keep up with the changes.

Really, copying to the "A Task" "B Task" and "C Task" could probably be skipped and they could go directly to the assigned people, but I figured it would be easier to write the code to split by tasks and copy/paste to the individual people (especially since each category needs to be grouped, and there could be a varying number of tasks each week). That would at least cut a lot of the work out.

Tasks.xls

[mojo249]

Hi Adam

I've got the macro to copy from the Tasks sheet to the various 'A Tasks', 'B Tasks' etc sheets:

The script looks starts in cell G2 on Tasks sheet and if the the cell value = 1 then it copies the range C2:E2 to Tasks A sheet and moves to the next row (G3). It then loops until it gets to an empty row (cell in column A of the row is empty).

The process is repeated for columns H and I of the Tasks sheet.

It's not clear, though, what the relationship between the tasks and persons are. How are Tasks A, B & C allocated to Persons A, B & C? In other words, do you also want the tasks copied onto the 'Person A', 'Person B' and 'Person C' sheets?

Dion

[adamsad1]

Awesome, thanks!

Each person is assigned different tasks project--in other works, one person may be responsible for payroll, another for AR, AP, and general ledger, another for purchasing and AP, and so on. They work in groups, so there is usually more than one person working on a function, so the tasks need to be seperated out to everyone responsible for the tasks.

I think with what you've given me I can tweek the code to work for the actual worksheets. I just had no idea how to get started.

Thanks again.

[mojo249]

You're welcome. Good luck