VBA codes to extract data into different worksheets

I have a few datasets. Dataset1 is in the format in an excel file (file 1) below as of Mar2016. There are 3 main product types (A, B and C) and each product type is categorized into 5 products with data from 2015/January to 2016/March. The headers for the product types (e.g. type A in B2 to G2) are merged cells. Full dataset 1 (original format) TypeA TypeB TypeC Amount 2015/January …….. 2015/December 2016/January 2016/February 2016/March 2015/January …….. 2015/December 2016/January 2016/February 2016/March 2015/January …….. 2015/December 2016/January 2016/February 2016/March Product1 11 16 21 10 15 20 14 25 16 13 24 15 3 6 75 2 5 74 Product2 4 6 8 3 5 7 11 46 13 10 45 12 57 2 4 56 1 3 Product3 2 3 6 1 2 5 37 13 8 36 12 7 11 16 28 10 15 27 Product4 6 9 10 5 8 9 11 46 6 10 45 5 46 24 13 45 23 12 Product5 11 26 8 10 25 7 16 24 13 15 23 12 42 11 16 41 10 15 As of Apr2016, I would have another new file (file 2) and the dataset would contain data from 2015/January to 2016/April (with 3 additional columns added for the current month 2016/April data for the 3 product types) Every month, there would be a new file, containing additional columns for the current month. Can I have the VBA codes to extract and process the data in the following steps automatically? 1. To extract the data for certain periods only (e.g. from January2016 to March2016) and paste into a new blank file (in worksheet 1) automatically Final format (worksheet 1) TypeA TypeB TypeC Amount 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March Product1 10 15 20 13 24 15 2 5 74 Product2 3 5 7 10 45 12 56 1 3 Product3 1 2 5 36 12 7 10 15 27 Product4 5 8 9 10 45 5 45 23 12 Product5 10 25 7 15 23 12 41 10 15 2. After the data are copied into worksheet1, the same table is duplicated in worksheet2 but without data. Set formula such the value in worksheet2 is equal to the value in worksheet1 divided by 10, e.g. Sheet!1B2/10. The cell references are the same for the 2 worksheets. Final format (worksheet 2) TypeA TypeB TypeC Amount 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March Product1 1 1.5 2 1.3 2.4 1.5 0.2 0.5 7.4 Product2 0.3 0.5 0.7 1 4.5 1.2 5.6 0.1 0.3 Product3 0.1 0.2 0.5 3.6 1.2 0.7 1 1.5 2.7 Product4 0.5 0.8 0.9 1 4.5 0.5 4.5 2.3 1.2 Product5 1 2.5 0.7 1.5 2.3 1.2 4.1 1 1.5 3. To duplicate worksheet 2 in worksheet 3 without formula (copy and paste the data as values) Cells are formatted to display figures in 1 decimal place. Final format (worksheet 3) TypeA TypeB TypeC Amount 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March Product1 1 1.5 2 1.3 2.4 1.5 0.2 0.5 7.4 Product2 0.3 0.5 0.7 1 4.5 1.2 5.6 0.1 0.3 Product3 0.1 0.2 0.5 3.6 1.2 0.7 1 1.5 2.7 Product4 0.5 0.8 0.9 1 4.5 0.5 4.5 2.3 1.2 Product5 1 2.5 0.7 1.5 2.3 1.2 4.1 1 1.5 Every month, there would be a new file, containing additional columns for the current month. The VBA codes should work for rest of the months and there is no need to write a new set of codes for each month. As I have a few datasets with quite similar format (slight variations) as Dataset1, preferably I hope that the VBA codes can allow flexibility in selecting any starting date and end date and any product number or even any Product type to extract the data, if technically feasible. Or otherwise, I am fine if different set of codes have to be customised and used for each datasets. Thank you for your help. Dataset 2 Can I have the VBA codes to extract and process the data in the following steps automatically? Full dataset 2 (original format) The headers for the product categories (e.g. category X in A5 to A9) are merged cells. Amount TypeA 2015/January …….. 2015/December 2016/January 2016/February 2016/March Cateory X Product1 11 16 21 10 15 20 Product2 4 6 8 3 5 7 Product3 2 3 6 1 2 5 Product4 6 9 10 5 8 9 Product5 11 26 8 10 25 7 Cateory Y Product1 11 16 21 10 15 20 Product2 4 6 8 3 5 7 Product3 2 3 6 1 2 5 Product4 6 9 10 5 8 9 Product5 11 26 8 10 25 7 Cateory Z Product1 11 16 21 10 15 20 Product2 4 6 8 3 5 7 Product3 2 3 6 1 2 5 Product4 6 9 10 5 8 9 Product5 11 26 8 10 25 7 1. To extract the data for certain periods only (e.g. from January2016 to March2016) and paste into a new blank file (in worksheet 1) automatically. Formatting shall remain (colour of text, highlights, font size, etc….) Final format (worksheet 1) Amount 2016/January 2016/February 2016/March Cateory X Product1 10 15 20 Product2 3 5 7 Product3 1 2 5 Product4 5 8 9 Product5 10 25 7 Cateory Y Product1 20 25 30 Product2 13 15 17 Product3 11 12 15 Product4 15 18 19 Product5 20 35 17 Cateory Z Product1 30 35 40 Product2 23 25 27 Product3 21 22 25 Product4 25 28 29 Product5 30 45 27 2. After the data are copied into worksheet1, the same table is duplicated in worksheet2 but without data. Set formula such the value in worksheet2 is equal to the value in worksheet1 divided by 10, e.g. Sheet!1B3/10. The cell references are the same for the 2 worksheets. In worksheet1, data is categorized by Category in Col A and then by Product type in Col B. Data are arranged according to ascending order of each Category and then list of Products. In 2nd worksheet, to copy the data from worksheet1 such that data are arranged according to each Product and then list of Category. Final format (worksheet 2) Amount 2016/January 2016/February 2016/March Cateory X Product1 10 15 20 Cateory Y Product1 20 25 30 Cateory Z Product1 30 35 40 Cateory X Product2 3 5 7 Cateory Y Product2 13 15 17 Cateory Z Product2 23 25 27 …. ….. ….. ….. …. Cateory X Product5 10 25 7 Cateory Y Product5 20 35 17 Cateory Z Product5 30 45 27 3. After the data are copied into worksheet2, the same table is duplicated in worksheet3 but without data. Set formula such the value in worksheet3 is equal to the value in worksheet2 divided by 10 and round to 0 decimal place, e.g. round(Sheet!1C31/10,0). The cell references are the same for the 2 worksheets. Cells are formatted to display figures in 0 decimal place. Final format (worksheet 3 & 4) Amount 2016/January 2016/February 2016/March Cateory X Product1 1 2 2 Cateory Y Product1 2 3 3 Cateory Z Product1 3 4 4 Cateory X Product2 0 1 1 Cateory Y Product2 1 2 2 Cateory Z Product2 2 3 3 …. ….. …. ….. ….. Cateory X Product5 1 3 1 Cateory Y Product5 2 4 2 Cateory Z Product5 3 5 3 3. To duplicate worksheet 3 in worksheet 4 without formula (copy and paste the data as values). Cells are formatted to display figures in 0 decimal place. 4. To copy entire worksheet 4 and paste as values into worksheet 5. Final format (worksheet 5) Amount 2016/January 2016/February 2016/March Cateory X Product1 1 2 2 Cateory Y Product1 2 3 3 Cateory Z Product1 3 4 4 Cateory X Product2 0 1 1 Cateory Y Product2 1 2 2 Cateory Z Product2 2 3 3 …. ….. …. ….. ….. Cateory X Product5 1 3 1 Cateory Y Product5 2 4 2 Cateory Z Product5 3 5 3 5. To copy and paste each of the Product types into next few worksheets and rename the worskheet name accordingly. If the Product name is too long to fit into worksheet name, to shorten the name based on the max no. of characters (e.g first few 100 characters). Final format (worksheet 6)-> worksheet name is Product1 Amount 2016/January 2016/February 2016/March Cateory X Product1 1 2 2 Cateory Y Product1 2 3 3 Cateory Z Product1 3 4 4 Final format (worksheet 10)-> worksheet name is Product5 Amount 2016/January 2016/February 2016/March Cateory X Product5 1 3 1 Cateory Y Product5 2 4 2 Cateory Z Product5 3 5 3

• › VBA code to copy data between sheets auto
• › [Solved] VBA codes to merge mutiple into 1 worksheets
• › VBA code to move multiple date list to single date list
• › Excel VBA code to move data
• › [Solved] VBA code to insert new row and copy from row above
• › VBA Codes to copy, transpose and paste data
• › [Solved] VBA code to insert specific no. of new row & copy from above