Excel VBA (Macros & Pivot Table)

LoopsLargeData.xlsm

– This file contains the TSLA bonds data. Try to use what you have learned in the previous classes to create a macro that creates multiple copies of the data (exclude the first 10 cells with the titles – we don’t want multiple copies of this part) by coping the data down in column A until you fill all cells to A655370 – see the complete results in the last sheet. You would want to complete that with the least number of steps possible (it could be done with as little as 13 iterations using a For… Next statement). After that you will apply the transpose macros we developed in our last class and see how they perform with much larger data set. We will record the time it takes for the different transpose macros in M1:M4 respectively – See the last sheet ‘Long Data’.

2013_2014_Sales_Data.xlsx
VBA_Copy_Paste.xlsm

– These two files are to be used in conjunction. You need to have both files open. The files contain sales data for two separate salespeople for different years contained in different sheets. We want to combine the data from all sheets and from both files into one sheet “All_Data” and create a Data Table and a Pivot Table – see the provided picture in this sheet for example of the final result.

Note that you should not move the actual 2013 and 2014 worksheets in the “VBA_Copy_Paste” workbook (manually or using VBA) – the “VBA_Copy_Paste” workbook should only have the original number of 5 worksheets. Your macro needs to directly combine (copy/paste) the data from all sheets and from both files into one sheet “All_Data” in the “VBA_Copy_Paste” workbook.

Try to use the macro recorder if you need to find the syntax for certain VBA operations and utilize what you have learned in the previous classes (use loops to automate repetitive operations, etc.) to create such macro by yourself.