Excel Calandar Pivot solution in VBA

Description This job is for mid to senior Excel/VBA people. Please do not apply if you paste code found from Google. Your code will be edited/maintained by other people, so it must be modular, clear and concise. Only take this job if you can complete it comfortably in 2 days. I need this job done quickly and have another similar larger job straight after it. I need an Excel Calendar Pivot created with layout and formatting performed in VBA. The Excel spreadsheet will need to: 1. Have the data pivoted, 2. A layout applied, and 3. To have formatting applied. This spreadsheet will have raw data refreshed regularly (from a database connection) in a ‘data only’ worksheet. I need VBA code to be written that transforms the data into a calendar style view on a new worksheet. Whenever the VBA code runs, it will create a new worksheet and loop through the refreshed data to create a new calendar view. This calendar view will be read only for users. I have attached two spreadsheets: 1. ‘SampleCalendarOutput.xlsx- this is similar to what the final product should look like 2. ‘SampleCalendarData.xlsx- this is sample data to base your code on. When code is ready for testing, I will provide a few hundred rows to test against VBA deliverables 1. Prepare Calendar Worksheet a. Delete any old worksheets named “Calendar” b. Create a new worksheet named “Calendar” 2. Transform/Pivot Data a. From the ‘Data’ worksheet: i. Populate ‘Calendar’ row 1 with column names: First name, Surname, Trade, date1, date2, date3, … dateX ii. Row onwards: populate data b. Create rows for each person, then populate the dates for the days they are working c. The first cell of any work should have the name of the site inserted. Every 7th day should have the site name inserted (makes it easier to read) i. The colour for the cell should be taken from the “SiteColour” sheet ii. Where a match for the site cannot be found, the “default” colour should be used d. Loop though the entire data worksheet to populate all people and the job calendars they are working on e. If a person has two jobs that overlap, then the overlapping cells should be black and populated with a star (i.e. *) 3. Format Data a. Set all column widths, so all data is visible b. Freeze the first 3 columns c. The original data in the ‘data’ sheet goes back about a month and is about 2 months calendar in the future. The users will want to look at old data, but would like to start at todays date. Can you get the default view to be centred on today’s date? d. Hide the ‘data’ and ‘sitecolour’ sheets Notes 1. If you wish to apply, please: 2. Read the notes carefully and make sure you understand 3. Make sure you can complete the bulk of this in 1 day and have a spare day for any modifications 4. Be ready to talk to me to discuss 5. If unforeseen changes we will pay for this 6. I have another similar larger scope that needs to be completed 5 days later. It will be 2, or 3 times the amount of work. Similar, but with more formatting. 7. If you do a good job, I will ask you for an estimate and only give the work to you. 8. There are 2 more scopes in the next 2 weeks after the second scope is complete 9. Please only apply if you are available and certain you can complete this work on time 10. I will choose someone in 12 – 24 hours to commence the work immediately 11. I look forward to working with you. 2 days