Dynamic filtering copy and pasting to another sheet using VBA

[raj saha]

I have file that want to filter, by product type, column B on the new deal tab, and then copy and paste the data to another sheet. At any time there can be anywhere from 1-5 (WTI, BRT, NG, RB, HO) different product types. I want to be able to create a macro that will dynamically know which products to filter when available, and then copy and paste them to there respective tabs. i have recorded a macro of the steps of what i am trying to accomplish. i also realize that the data i copy will be in different cells each time, so i will need to make that dynamic as well.

i have attached a file to make it easier to understand what i am doing

Any and all help is greatly appreciated. thank you.

 

[Hui]

Have a read of these posts :

Move Data to other Sheets
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/
Advanced Filter Move Data to other Files
http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

 

[raj saha]

thank you. they were very helpful. i am using the Move Data to other Sheets
http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/ code. it works great. again, thank you very much.