Extracting selected data into new worksheets

[dcaraher]

I have a workbook, see attached example, which has multiple columns. I want to extract the data for a certain criteria, in this example column E "product".

I then want to take all of the data in columns A to L for the chosen criteria e.g. product 1696 and place it in a new worksheet.

I want to do this for every unique product. The example I have given only shows 2 products and limited rows, in reality I could have 50-60 products with hundreds of rows per product.

Is there a way of achieving this?

Many Thanks

[StephenR]

Try this:

Please Login or Register  to view this content.

[dcaraher]

Stephen, that is excellent. I'm no expert in analysing the code but a quick question.

Does this code work for unlimited product and unlimited number of rows/columns or will I need to adapt it if my ranges increase.

Many Thanks

[StephenR]

My pleasure.

It should work for any amount of data and any number of products as long as your products remain in column E (the 5 in the AutoFilter line) and there are no completely blank rows in your data.

[dcaraher]

Stephen, That has ben so helpful thank you very much. How do I show this topic as solved?

[StephenR]

For future reference, see "How To" at the top of the page.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save