Pivot Table Friendly Formatting
 

Our Sales Managers have created a form for our associates to use for entering
their sales/contacts so that they can be tracked. They would like me to come
up with a way to easily summarize all the info (by week, month, ytd), but the
form they have created is not Pivot Table friendly. It is one worksheet made
up into multiple sections for the different categories that they are
tracking. For instance the Headers for one section are located at row 5,
while the headers for the next section are located at row 12. It's going to
be a weekly report, but the way they have it setup, it can only account for 1
week at a time. I have changed the layout so that all the headers go across
row 1, and week 1's info would be entered in row 2, week 2's info entered in
row 3, etc... That way it can easily be setup for a Pivot Table. The Sales
Managers don't like it, because they don't think it looks good. I figured
the only way to do it would be to link each cell in their form to the
corresponding cell in the form that I've created. The only problem is that
once they start a new week it will automatically replace the info for the
previous week. Is there any way to write a macro that could save all the
values at the end of the week on the form I created (which is linked to the
Sales Managers form) and then tell it to move down one row so that info can
be entered for the upcoming week? Or is this unrealistic?