Hi,

How do i make a chart based on data in a pivot table, without it resulting in a PivotChart.

I was able to do it once; but am unable to now.

Thanks.
C

Hi C,

This is how i charted a pivotable

Sub ChartData()
'Chart the Data which the User Selects
Dim dblLastRow As Double, intLastCol As Integer
Dim strMyRange As String, StrChartTitle As String

'Create Pivot Table to Chart use procedure above
Call PivotTable

Worksheets("Pivot").Activate
dblLastRow = Range("A6").End(xlDown).Row - 1
intLastCol = Range("A7").End(xlToRight).Column + 63
StrChartTitle = Indicator & Chr(10) & RowName & " vs " & ColName
strMyRange = "A6:" & Chr(intLastCol) & dblLastRow

'create chart (Column Variety)
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Worksheets("Pivot").Range(strMyRange), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

'add titles to chart
With ActiveSheet.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Characters.Text = StrChartTitle
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = RowName
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "no.of patients"
End With
'sets the Height and Width of the Chart
With Worksheets("Chart").ChartObjects(1)
.Height = 200
.Width = 500
.Top = 100
.Left = 10
End With
'select which sheets you want to show
If WhichDoc = True Then
Worksheets("Pivot").Activate
End If
End Sub

My Pivot Table starts at Cell A6 to allow for images at the top of the screen this is for any pivot table which has been produced by the pivottable sub routine.

I hope this helps and realise this is probably not the best way to do it.
I charted the data from the pivot table rather than from the pivot itself.
This works for 97(all i use im afraid)

David

Ive upgraded to 2000 in the past couple of days and now know how to remove the pivotfields from a chart

after you have created a pivotchart Right Click on one of the fields and choose the option Hide Pivotchart field buttons.

This will display the chart as just a chart and not a pivotchart

David

oh ..thanks again..

so simple...too tired to think of it...