Excel Macros

The Cinemas will sent daily sales report in PDFs. We need to then manually input the data into excel forms. There are normally 6 sectional reports for each day, each section has both tickets and candy bar sales. The figures in each sectional report are the total of all prior sections. For example if section 3 report shows an item has a sales of $540 it means that item has total sales of $540 from section1 to secton3. We want to automate the data entry by using excel macros. Process described as follows; 1. Using Adobe convert each PDF into excel worksheet- file name as the section name. (there are 2 type of file name i.e 20150306s1ws- it means date-section1- what is selling, cb20150306s1ws- it means Candy Bar-date-section 1- What is selling) 2. Cleaned up the excel files by deleting repeated/ blank/ irrelevant rows and columns. 3. Prepare data- insert a column for each sheet and input the value of the file name of each worksheet in to cells of the insert column. So each row of data for the sheet has a common identifier. 4. Transfer the data of the day into a temp table. 5. In the temp table, calculate sales for each sections by subtracting the value of the prior sections. ( i.e. to derive section 6 figures could be using section 6 figure minus section 5 figures... so on and so forth for the rest ...). Then correct each section sales data. 6. Transfer the data into a master table. Then delete the temp table. 7. Extract data from the master table to the forms required for further distributions/ processing. ( master table will be kept for further use of analysis) Attached ( PDFs, converted Worksheets, Required form for distribution).