Need assistance on vba macro for Yearly Flash report

Hi,

I need a vba macro, which will automate the manual work. The sample file attached, consist of Master Data, which contains the raw data, which keep on adding every week.

First step - In Sheet "Table", we have a pivot table. Segregation is done based on sub category, beside the table there is 4 sub heading as NA,EU,FE and SA, where NA marketplace is 1, EU marketplace is 2, FE marketplace is 3 and SA marketplace is 5. The calculation should be done like, NA whose marketplace is 1 for the respective sub category, would check in pivot table for 1 for the same sub category, then it should check the count value of marketplace and divide it by total of that sub category. Eg- NA whose marketplace is 1, with count value as 63 and total as 322,so Calculation : "=Count of Marketplace value of 1/Total of the Count Value of that sub category". This should be done for all 4 marketplace and for all category. As present in the Table sheet.

Second Step - This Calculation should be added among the 3 sheet, i.e., Hardline, Softline and Consumables. This 3 sheet have the sub category. So Value should be added to the respective sheets based on the sub category. Placement of value should be exact for the sub category. Eg - Accessories NA value should be added to the Softline sheet Accessories NA. Raw data consist of only weekly data. We need to calculate monthly basis also, i.e, summation of 4 week data for 1 month. We need to calculate quarterly basis also, i.e., summation of 4 month.

Third Step - Value from this 3 sheet, should be added to the Final_Data vertically based on the headings given.

Thanks in advance for looking into the query.

PTD Automation.xlsx


Thanks and Regards,
Aswini Dubey

No sample file attached!