SetSourcedata of pivotchart on a pivot table that changes
I am having troubles with a pivotchart that is based upon a pivottable that when refreshed can change size in terms of rows and columns. When recording a macro, the destination is hardcoded as follows:
Charts.Add
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Sheets("CMR Count by Mgr & Type table"). _
Range("A1:BD20")
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"CMR Count by Mgr & Type chart"
where Range("A1:BD20") is a actually a pivottable created in a previous subroutine. Depending upon the data, the actual size of the pivottable may be different than what is in this code.
How do I dynamically create a pivottable that will have an unknown number of rows and columns and associate a pivotchart with the pivottable?
