Automated Dashboard Generation

So information from an Access query is transfered to an excel spreadsheet every day at 5:30AM. Every time one of those workbooks are created, I want there to automatically be a pivot table on another sheet that shows all the same information. The number of records in each worksheet changes every day, but the columns do not. I need to know how to create the appropriate macro to do this, and I would also like to know if it is possible to insert a scroll feature in the table so that when I expand a field in the row labels, the list doesn't look ridiculously long. You might know what I mean if you're familiar with creating pivot tables in Access 2007; I basically want the same feature. I also need to know if it's possible to add conditional formatting to cells so that I can color code the table. Assistance of any sort would be greatly appreciated. If you know the VBA code, that works too.

What difference does it make? The excel files already get created on a regular basis. It's just part of the process here at my office. I just need to know how I can automate the dashboard.