Copy & paste table

[nc]

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.

 

[Bassman62]

Hi,

Check out question #7 on this page from Ashish.
http://ashishmathur.com/knowledgebaseII.aspx
You can download a copy of the file he developed.
It does not use macros but will accomplish your goal.
Hope this helps.

Dave

 

[Bassman62]

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

 

[nc]

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]

First note that "History" is reserved by Excel and can not be used as a
Sheet Name. If you want to use History as a department change it by adding
a -(dash) or something else.
Regarding changes to the macros:
All the macros that use the variable "My_Range" will need to be changed to
meet your needs. If your example starts in A11 then change the line
Set My_Range = Range("A11" & LastRow(ActiveSheet)) to
Set My_Range = Range("A11:E" & LastRow(ActiveSheet))
I would not try to change the code to copy specific columns for specific
departments.
Instead, set up each sheet with the proper Department Names and include All
the column headers beginning at A1. On each department sheet Hide the
columns you don't want to see.
Then use the Sub "Copy_To_Worksheets_2()"
The only other change required to the code will be to remove the line (or
mark as a comment by placing an ' apostrophy in front of)
".PasteSpecial Paste:=8"
This part of the code sets the column width of the destination range. With
it removed, your hidden columns will remain hidden.
These macros are advanced and call User Defined Functions. If you are new to
VBA, see these sites and others.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

Best Wishes
Dave