Description
Overview
Your manager has instructed the IT Department to have a file sent to you on a weekly basis. The file isgenerated by the companys customer relationship management (CRM) software and is emailed to you asa comma separated value (.csv) formatted file. It contains a list of property insurance policies that expirenext year (2022). Management wants this report so that they can monitor renewal activity and, of course,the subsequent impact on sales. The file contains the policy holder identification number, the policyexpiry date, the type location (i.e. urban or rural), the region, the insured value, construction type,business type, and whether or not the policy includes a flood and/or earthquake provision.
One of your duties is to take this .csv file and transform it into a report that can be embedded in a worddocument along with some additional analysis. Given that you must process this report every week, youhave decided to record a macro to automate the formatting.
The Problem
Select the data on the current worksheet. Create a macro, naming your macro FormatReport, assigningit a hotkey of ctrl+shift+F, and save it in This Workbook.Create a macro that will accomplish the following:
1. Copy the data to a new worksheet2. Rename the worksheet FormattedReport3. Format columns A through J to be 15 units wide4. Insert 4 blank lines at the top of the report5. Insert Weekly Expiry Report in cell A16. Select the text you just entered and increase the font size to 167. Select cells A1 through J1, Merge and Center the text8. Select cell A2 and enter Week of: 03/15/20219. Increase the size of the text entered in step 8 to 14 units10. Select cells A2 through J2, merge and center11. Insert Prepared by: [Your Name] in cell A3, where [Your Name] is . . . your name.12. Increase the font size of the text entered in the previous step to 12 units13. Select A3 through J3, Merge and Center14. Select the column headings in A5 through J5a. Make the text boldb. Center the textc. Underline the cells15. Select the data in columns C, D, E, G, H, I and J and right justify the textIntroduction to Informatics and AnalyticsINFO 3301Online at: elearn.isu.edu16. Select the data in column F and format as currency17. Select the data in column B and apply conditional formatting such that any date less than oneyear from the date of the report is highlighted in yellow.
Finally, delete the FormattedReport tab and save the workbook as FirstIntitialLastNameHW7.xlsm,where FirstInitialLastNameHW7 is the first letter of your first name, followed by your last name,followed by HW7. Make sure that you save your file as an Excel Macro-Enabled Workbook. Submit yourcompleted assignment to Moodle.
Done correctly, I should be able to open your workbook, highlight the data, press ctrl+shift+F , and havethe FormattedReport tab will be recreated.
