I am trying to find a more efficient way to create a sales report with forecasts and Quarterly totals for each sales rep.
• I will need to update monthly actual sales (sales people assignments may change)
• I will need to create a separate worksheet & file for each sales rep (19 sales reps w/up to 200 customers each)
• I will receive the files back from each sales rep to update forecast and prospects

I received original data in excel with columns for monthly sales and rows with the salesperson’s ID (each has several), the customer ID & Name
CUSTOMER ID GROUP & Top 20 Salesperson ID CUSTOMER NAME 1/31/2015 2/28/2015
I figured out how to merge that with the reports from 2013 & 2014

My boss has requested the report I send to them looks like this:
All the info from the original file + below for all quarters
Jan 13 Jan 14 Jan 15 Jan 15 Q1 13 Q1 14 Q1 15 Q1 15
Actual Actual Forecast Actual Actual Actual Forecast Actual

I then need to create separate worksheets and files for each sales rep with additional blank rows for prospects, Totals, & protection in certain areas.

My current process is to set up the original report to match the end report, then merge rows and look for data that doesn’t match and adjust.
I then filter & copy & paste into a separate workbook for each sales rep. (I had found a macro online that automatically creates a separate worksheet for each rep but it does not copy the formulas)
I’ve created macros that format, add the prospect lines and totals, and protect certain areas.

There must be an easier and faster way. I’ve tried pivot tables but it doesn’t keep the same formatting.