• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Need Excel help with pivot charts and reports

minendo

minendo

Elite Member
I know I will need coding of some sort in order to accompolish what I need, but I am not sure how to do it.

The situation is as follows:

Currently I have 13 locations with 6 attributes per location that each need a chart generated off of a pivot table. However, with the pivot table I can only view one chart at a time.

What I want to see is the total 96 charts generated on 1 spreadsheet in a 2*48 format.

How can I accompolish this?
 
This sounds similar to situations I have run into, and I have yet to find a truly satisfying way of doing this. The best approach I have found to date does involve macros, so I hope you're comfortable with VB.


The macro needs to loop your chart through each of the desired combinations of locations and attributes. At the end of each loop (i.e. after the chart reflects the information for that location/attributes), then you should:
  • 'select' the range of cells that includes your embedded chart and then use CopyPicture to take a snapshot (i.e. bitmap) of it.
  • in order to paste the snapshot back into excel, you need to 'activate' a ChartObect and then ActiveChart.Paste.
The last time I did this, the ChartObject was in a different workbook and I was exporting that ChartObject (as a GIF) to its own named file on my hard drive (which meant I used that same ChartObject for every loop). I suppose you could define 96 ChartObjects, but I'm not sure how you arrange them on a single sheet.

I'm hoping someone else has a better idea!
 
Last edited:
Why not just set up all the charts on one sheet, then put the data on a second sheet.
Then you could set up each chart to reference whichever data cells you need from the second chart.

Assuming the data set is going to be the same size whenever it's updated, all you would need to do to update all the charts is to just paste the new data set on the second sheet - all the charts would automatically be updated with the new data.

Maybe I don't have a correct picture of what you're trying to do, but I've found that Pivot tables tend be get overused (i.e. not really required).
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top