I have a large workbook with one worksheet for raw data, and many other worksheets, each containing one pivot table pulling from the same data.
Each pivot table is a bit different, as it relates to the business of a particular sales rep. These reports are 'eyes only,' i.e. each sales rep is only allowed to see their own data.
So then (with VBA), I convert each pivot table worksheet into a flat, single-worksheet document (basically, copying the pivot table, then pasting values and number formats), and then each sales rep gets their report and nothing else.
The problem is that I don't want to send the reps flat reports. I want to send them the pivot tables, so that if they don't like the way I arranged the data, they can mess around with it.
But how can I? It seems to me that I would have to break up the raw data into pieces and create a separate data sheet for each rep, from which would be made a pivot table for that rep. I'm sure there is a way to do that with code, but... is that really necessary? Is there a better way? Surely this is a relatively common problem.
Any thoughts would be greatly appreciated.
I'm using Excel 2007 on Windows XP, and so is everyone else.
Each pivot table is a bit different, as it relates to the business of a particular sales rep. These reports are 'eyes only,' i.e. each sales rep is only allowed to see their own data.
So then (with VBA), I convert each pivot table worksheet into a flat, single-worksheet document (basically, copying the pivot table, then pasting values and number formats), and then each sales rep gets their report and nothing else.
The problem is that I don't want to send the reps flat reports. I want to send them the pivot tables, so that if they don't like the way I arranged the data, they can mess around with it.
But how can I? It seems to me that I would have to break up the raw data into pieces and create a separate data sheet for each rep, from which would be made a pivot table for that rep. I'm sure there is a way to do that with code, but... is that really necessary? Is there a better way? Surely this is a relatively common problem.
Any thoughts would be greatly appreciated.
I'm using Excel 2007 on Windows XP, and so is everyone else.
