VBA to extract data from a sheet to new workbok

[agrwlnishant]

Excel 2007

[TABLE="width: 1586"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Voucher Number[/TD]
[TD]Date[/TD]
[TD]Party Name[/TD]
[TD]Item Name[/TD]
[TD]Item Group[/TD]
[TD]Item Description[/TD]
[TD]MRP[/TD]
[TD]Item HSN[/TD]
[TD]Item GST[/TD]
[TD]Acutal Quantity[/TD]
[TD]Rate[/TD]
[TD]Unit[/TD]
[TD]Discount[/TD]
[TD]Discount Amount[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Chocolate @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]47.67[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]572.04[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Hazelnut @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]47.67[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]572.04[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Orange @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]47.67[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]572.04[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Strawberry @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]47.67[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]572.04[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Vanilla @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]47.67[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]572.04[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Almonds @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Banana @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Blue @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Raspberry @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Red @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Rose @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Strawberry @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Vanilla @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]001/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Camp - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]SF Harveys 28ml Essence - Yellow @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]002/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Super Market Grocery Su Pvt. Ltd[/TD]
[TD]Whistle 60g Multigrain Puffs - Cheese @60[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]002/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Super Market Grocery Su Pvt. Ltd[/TD]
[TD]Whistle 60g Multigrain Puffs - Masala @60[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]37.50[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 60g Cheesy Makhana (No Onion Garlic) @100[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]18[/TD]
[TD]58.04[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1044.72[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 60g Multigrain Puffs - BBQ @60[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]24[/TD]
[TD]34.82[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]835.68[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 60g Multigrain Puffs - Cheese @60[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]24[/TD]
[TD]34.82[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]835.68[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 60g Multigrain Puffs - Masala @60[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]21[/TD]
[TD]34.82[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]731.22[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 60g Peppery Makhana @100[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]19[/TD]
[TD]58.04[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1102.76[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 60g Sunshine Makhana @100[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]100[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]24[/TD]
[TD]58.04[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1392.96[/TD]
[/TR]
[TR]
[TD]003/S/18-19/PN[/TD]
[TD]03-09-2018[/TD]
[TD]Viman Nagar - Dorabjee & Co. Pvt. Ltd.[/TD]
[TD]Whistle 75g Chana Chor - Chilli Lime @40[/TD]
[TD]Whistle[/TD]
[TD] [/TD]
[TD]40[/TD]
[TD]2106[/TD]
[TD]12[/TD]
[TD]24[/TD]
[TD]23.21[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]557.04[/TD]
[/TR]
[TR]
[TD]004/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - M/s N.V. Cashew[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Chocolate @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]198.32[/TD]
[/TR]
[TR]
[TD]004/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - M/s N.V. Cashew[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Hazelnut @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]198.32[/TD]
[/TR]
[TR]
[TD]004/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - M/s N.V. Cashew[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Orange @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]198.32[/TD]
[/TR]
[TR]
[TD]004/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - M/s N.V. Cashew[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Strawberry @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]198.32[/TD]
[/TR]
[TR]
[TD]004/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - M/s N.V. Cashew[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Vanilla @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]198.32[/TD]
[/TR]
[TR]
[TD]005/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - Joglekar Foods[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Hazelnut @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]148.74[/TD]
[/TR]
[TR]
[TD]005/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - Joglekar Foods[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Vanilla @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]148.74[/TD]
[/TR]
[TR]
[TD]005/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - Joglekar Foods[/TD]
[TD]SF Harveys 28ml Essence - Banana @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]39.00[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]117.00[/TD]
[/TR]
[TR]
[TD]005/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - Joglekar Foods[/TD]
[TD]SF Harveys 28ml Essence - Raspberry @59[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]59[/TD]
[TD]3301[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]39.00[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]117.00[/TD]
[/TR]
[TR]
[TD]006/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - S.P. Caterers[/TD]
[TD]BA Delicious 1000g Chicken Cheese Garlic Fingers @468[/TD]
[TD]Delicious - Baramati Agro[/TD]
[TD] [/TD]
[TD]468[/TD]
[TD]1602[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]330.94[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]661.88[/TD]
[/TR]
[TR]
[TD]006/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - S.P. Caterers[/TD]
[TD]BA Delicious 1000g Chicken Nuggets (Classic) @420[/TD]
[TD]Delicious - Baramati Agro[/TD]
[TD] [/TD]
[TD]420[/TD]
[TD]1602[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]281.25[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]562.50[/TD]
[/TR]
[TR]
[TD]006/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Kothrud - S.P. Caterers[/TD]
[TD]BA Delicious 1000g Chicken Pop Ons (Popcorn) @417[/TD]
[TD]Delicious - Baramati Agro[/TD]
[TD] [/TD]
[TD]417[/TD]
[TD]1602[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]279.24[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]558.48[/TD]
[/TR]
[TR]
[TD]007/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Shivane - Mananjali Sales[/TD]
[TD]BA Delicious 250g Chicken Pop Ons (Popcorn) @130[/TD]
[TD]Delicious - Baramati Agro[/TD]
[TD] [/TD]
[TD]130[/TD]
[TD]1602[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]92.86[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]928.60[/TD]
[/TR]
[TR]
[TD]007/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Shivane - Mananjali Sales[/TD]
[TD]BA Delicious 300g Chicken Rings @155[/TD]
[TD]Delicious - Baramati Agro[/TD]
[TD] [/TD]
[TD]155[/TD]
[TD]1602[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]110.71[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]885.68[/TD]
[/TR]
[TR]
[TD]007/S/18-19/PN[/TD]
[TD]04-09-2018[/TD]
[TD]Shivane - Mananjali Sales[/TD]
[TD]BA Delicious 500g Chicken Nuggets (Classic) @255[/TD]
[TD]Delicious - Baramati Agro[/TD]
[TD] [/TD]
[TD]255[/TD]
[TD]1602[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]182.14[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1821.40[/TD]
[/TR]
[TR]
[TD]008/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Cash Invoice[/TD]
[TD]MC Cocon 1500g Jelly - Lychee (100 Cups) @300[/TD]
[TD]Cocon - Magnum Chocolatier[/TD]
[TD] [/TD]
[TD]300[/TD]
[TD]17049010[/TD]
[TD]12[/TD]
[TD]6[/TD]
[TD]169.64[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1017.84[/TD]
[/TR]
[TR]
[TD]008/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Cash Invoice[/TD]
[TD]MC Cocon 240g Pudding - Mixed (3 Cups) @60[/TD]
[TD]Cocon - Magnum Chocolatier[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD]17049090[/TD]
[TD]12[/TD]
[TD]32[/TD]
[TD]41.79[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1337.28[/TD]
[/TR]
[TR]
[TD]008/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Cash Invoice[/TD]
[TD]MC Cocon 708g Pudding - Mixed (6 cups) @150[/TD]
[TD]Cocon - Magnum Chocolatier[/TD]
[TD] [/TD]
[TD]150[/TD]
[TD]17049090[/TD]
[TD]12[/TD]
[TD]16[/TD]
[TD]104.46[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1671.36[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]MC Win2 70g Magic Crunch - Chocolate @50[/TD]
[TD]Win2 - Magnum Chocolatier[/TD]
[TD] [/TD]
[TD]50[/TD]
[TD]19059090[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]33.05[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]66.10[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]MC win2 70g Magic Crunch - Strawberry @50[/TD]
[TD]Win2 - Magnum Chocolatier[/TD]
[TD] [/TD]
[TD]50[/TD]
[TD]19059090[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]33.05[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]66.10[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]MC win2 70g Magic Crunch - Vanilla @50[/TD]
[TD]Win2 - Magnum Chocolatier[/TD]
[TD] [/TD]
[TD]50[/TD]
[TD]19059090[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]33.05[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]66.10[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 110g Wafer Pouch - Chocolate @99[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]99[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]65.44[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]130.88[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 110g Wafer Pouch - Orange @99[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]99[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]65.44[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]130.88[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 110g Wafer Pouch - Strawberry @99[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]99[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]65.44[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]130.88[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 110g Wafer Pouch - Vanilla @99[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]99[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]65.44[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]130.88[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Chocolate @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]5[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]247.90[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Hazelnut @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]5[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]247.90[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Orange @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]5[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]247.90[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Strawberry @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]5[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]247.90[/TD]
[/TR]
[TR]
[TD]009/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Salisbury Park - Tara Mulchand Sweets[/TD]
[TD]SF Harveys 150g Wafer Biscuit - Vanilla @75[/TD]
[TD]Harveys - Silverstar Food[/TD]
[TD] [/TD]
[TD]75[/TD]
[TD]1905[/TD]
[TD]18[/TD]
[TD]5[/TD]
[TD]49.58[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]247.90[/TD]
[/TR]
[TR]
[TD]010/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Kondhwa - Foodie[/TD]
[TD]Omay 115g Beaten Brown Rice - Desi Mix @120[/TD]
[TD]Omay[/TD]
[TD] [/TD]
[TD]120[/TD]
[TD]2008[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]85.71[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]171.42[/TD]
[/TR]
[TR]
[TD]010/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Kondhwa - Foodie[/TD]
[TD]Omay 140g Beaten Moong @90[/TD]
[TD]Omay[/TD]
[TD] [/TD]
[TD]90[/TD]
[TD]2008[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]64.29[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]128.58[/TD]
[/TR]
[TR]
[TD]010/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Kondhwa - Foodie[/TD]
[TD]Omay 160g Solid Soyabean @90[/TD]
[TD]Omay[/TD]
[TD] [/TD]
[TD]90[/TD]
[TD]2008[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]64.29[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]128.58[/TD]
[/TR]
[TR]
[TD]010/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]Kondhwa - Foodie[/TD]
[TD]Omay 165g Oats & Moong Mix @175[/TD]
[TD]Omay[/TD]
[TD] [/TD]
[TD]175[/TD]
[TD]2008[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]125.00[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]011/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]MG Road - The Taste Factory[/TD]
[TD]SF Kings Coffee 280ml Cold Coffee - Latte @139[/TD]
[TD]Kings Coffee - Silverstar Food[/TD]
[TD] [/TD]
[TD]139[/TD]
[TD]2202[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]96.80[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]387.20[/TD]
[/TR]
[TR]
[TD]011/S/18-19/PN[/TD]
[TD]05-09-2018[/TD]
[TD]MG Road - The Taste Factory[/TD]
[TD]SF Kings Coffee 280ml Cold Coffee - Mocha @139[/TD]
[TD]Kings Coffee - Silverstar Food[/TD]
[TD] [/TD]
[TD]139[/TD]
[TD]2202[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]96.80[/TD]
[TD]Pcs[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]387.20[/TD]
[/TR]
</tbody>[/TABLE]


This is a sample data of my report.
In this, I have to usually put a filter in "Item Group" Column with Contains = "Whichever name after the = sign"
Then I copy the data in to a new file along with the heading row and save it.

Is there any way to make a VBA wherein it asks me the "Contains =" Filter every time I run it and then proceeds to filter, copy and paste in to a new file (with all the existing formatting) for me to manually save?

Would be of great help as I generally take 1.5 to 2 hrs with doing it my way.

 

[igold]

Does this do what you want...

Sub FilterSave()


    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim arr, hdr, strSearch
    Dim x As Long, lRow As Long, i  As Long
    
    Application.ScreenUpdating = False
    hdr = Range("A1:O1")
    lRow = Cells(Rows.Count, 5).End(xlUp).Row
    arr = Range("E2:E" & lRow)
    With CreateObject("Scripting.Dictionary")
    For x = LBound(arr) To UBound(arr)
        If Not IsMissing(arr(x, 1)) Then .Item(arr(x, 1)) = 1
    Next
    strSearch = .KEYS
    End With
    For i = LBound(strSearch) To UBound(strSearch)
        With ws
            .AutoFilterMode = False
                With .Range("E1:E" & lRow)
                    .AutoFilter field:=1, Criteria1:=strSearch(i)
                    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy
                End With
            Workbooks.Add
            Range("A2").PasteSpecial Paste:=xlPasteAll
            Range("A1:O1") = hdr
            Range("A1").Select
            Dim workbook_Name As Variant
            workbook_Name = Application.GetSaveAsFilename
            If workbook_Name <> False Then
                ActiveWorkbook.SaveAs Filename:=workbook_Name
            End If
            ActiveWorkbook.Close
            .AutoFilterMode = False
        End With
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[Logit]

.
agrwlnishant & igold

Here is an automated version of igold's macro.

The user needs to double-click the "Item Group Name" (rows 2 till end of rows) desired. Example : If you are wanting to save all of the "Omay" rows to a separate workbook,
double-click on the "Omay" in Col E / Item Group. The macros will automatically create a workbook saved with the name "Omay", copy all the rows pertaining to "Omay" in Col E,
close the newly saved workbook and return the user to Sheet 1 in the "Filter Visible Rows" workbook.

You can change the name of the workbook ("Filter Visible Rows") to anything you like. If you want the saved workbooks containing the "Item Group" rows to be named something else,
these macros won't work. It will need editing.


Paste this macro into the Sheet 1 module :

Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngTable As Range
    Dim rngData As Range
    Dim iColumn As Integer
    On Error Resume Next
    Application.ScreenUpdating = False
    Set rngTable = Range("mydata")
    With rngTable
        Set rngData = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
        If Not Application.Intersect(ActiveCell, rngData.Cells) Is Nothing Then
            iColumn = ActiveCell.Column
            If ActiveSheet.AutoFilterMode = False Then
                .AutoFilter
            End If
            If ActiveSheet.AutoFilter.Filters(iColumn).On = True Then
                .AutoFilter Field:=iColumn
            Else
                .AutoFilter Field:=iColumn, Criteria1:=ActiveCell.Value
            End If
        End If
    End With
    Set rngData = Nothing
    Set rngTable = Nothing
    Application.ScreenUpdating = True
    FilterSave
End Sub

Paste this macro into a Routine Module :

Option Explicit
Sub FilterSave()




    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim arr, hdr, strSearch
    Dim x As Long, lRow As Long, i  As Long
    Dim NewWBName As String
    
    NewWBName = ThisWorkbook.Path & "\" & Selection & ".xlsx"
    
    Application.ScreenUpdating = False
    hdr = Range("A1:O1")
    lRow = Cells(Rows.Count, 5).End(xlUp).Row
    arr = Range("E2:E" & lRow)
    
    With CreateObject("Scripting.Dictionary")
        For x = LBound(arr) To UBound(arr)
            If Not IsMissing(arr(x, 1)) Then .Item(arr(x, 1)) = 1
        Next
        strSearch = .KEYS
    End With
    
        With ws
            .Cells.SpecialCells(xlCellTypeVisible).Copy
    
            Workbooks.Add
            
            Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ActiveSheet.UsedRange.Columns.AutoFit
            ActiveSheet.Range("A1:O1").Font.Bold = True
            ActiveSheet.Range("A1").Select
            
            ActiveWorkbook.SaveAs NewWBName
             
            ActiveWorkbook.Close
            .AutoFilterMode = False
            
        End With
  
            ws.Activate
            ws.Range("A1:O1").AutoFilter Field:=5
            Application.Goto Reference:=Worksheets("Sheet1").Range("A1"), scroll:=True
              
    Application.ScreenUpdating = True
    
End Sub

Download sample workbook : https://www.amazon.com/clouddrive/share/Oi61etJdp3R2JdwkvLe7R6HlTq5bYtR7oJVHc9yl90Z