I have a large excel 2010 based data file sent by my retail partner.
The first 5 columns have the list of my SKUs being sold by them, currently it is 152 and it is variable month on month basis. the table headers in cells A5:e5 is ( a5) Brand, ( b5:C5 merged) Article, (d5) Old Article Number, ( e5 ) Product segment. The cells A1:D4 are merged and empty.
The next 2 columns indicate to me a store no. and its sale in pcs. and sale in value. these two columns are repeated for the 98 stores that the product is being sold in. Cell F1 and G1 have the store code, F2 and G2 have store address, F3 G3 have store area in sq. meters, F4 is quantity sold and cell G4 has Net Item Sales ( INR ) as the headers.
In order for me to make my data BASE for pivot table, I need to consolidate this data into rows.
Currently I have a sheet titled MAIN DATA from supplier. I open another worksheet by name WORKING DATA, in which I copy the THREE columns ( B, C, D ) and 152 rows, paste it on the A2 cell of WORKING DATA sheet. Then I take the data of the sales from column 5 and 6 for the respective store and paste it against the SKU list. In cell a1 I copy the store code FROM CELL F1 and then copy it down to the cell A152.
Now again I go to the sheet titled MAIN DATA and copy the THREE columns ( B, C, D ) and 152 rows, paste it on the A153 cell of WORKING DATA sheet. Then I take the data of the sales from column 7 and 8 for the respective store and paste it against the SKU list. In cell A153 I copy the second store code FROM CELL H4 and then copy it down to the cell A304.
I keep doing this for all the 98 stores and it is a nightmare.
Can this process be automated using a MACRO? Since I am new to VBA, could you also tell me how to alter the col. nos. and row nos. in case the main data file from the seller is in a new format. e.g. if they have added a new store to make total store count to 99 and reduced items or incresed items from 152 to 151 or 153. This is important as the rows to be copied is changing.
I am working on WINDOWS platform and EXCEL 2010.
ANY HELP ON THIS WOULD BE REALLY APPRECIATED.
THANKS IN ADVANCE
you can use macro recorder to get a template. it would be easier to help, if you could post a sample workbook at some file sharing site and post back the link to the file. It would be easier for you to explain and others to understand
