Thanks. I saw these solution. I would like to know how to change the code
to chose the columns to copy & paste the releavant deaprtment to their
specific worksheet.

for example

Department:History column: Department, Product, Quantity
Department:Law column: Department, Colour, Amount
etc.


"Bassman62" wrote:

See Ron's site for a macro solution.
http://www.rondebruin.nl/copy5.htm
http://www.rondebruin.nl/copy5_4.htm


"nc" wrote in message
...
I have the following table,

I would like to paste the data on different worksheets based on department
using macros.

Example of data,

Department Product Colour Qty Amount
History Prod1 Red 20 100
Law Prod3 Blue 10 200
Lang Prod3 Red 20 300
Design Prod4 Blue 30 100


For example, paste to worksheet History, Law, Lang and Design, maybe
different columns (it is fine if hardcoded in the macro) for the each
departments. Plus I will also need to paste the to say worksheet History1
with different columns to the worksheet History, again maybe for more than
one department.

The most efficient codes will be appreciated.