Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: Charts from a Pivot Table

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Charts from a Pivot Table

    Originally posted by ontheDB
    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

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Charts from a Pivot Table

    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

  4. #4
    Join Date
    Sep 2003
    Posts
    102

    Re: Charts from a Pivot Table

    Originally posted by DavidCoutts
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •