Need for categories to be displayed in chart

[Ted Mifflin]

I two problems with this program below. Although it does print the data
correctly on each of eleven sheets, I now only need the program to print
on sheets 2 and 9. Any idea how to change this FOr..Next staetment to
add that flexibikity?

The second problem I have is that the program doesn't display the data
category names on the finished chart. (I have data that exist in 6
columns (x and five y variables) and the category names are listed
directly above the five y variables). Unfortunately, the program only
prints the Legend with: Series 1, 2, 3, 4, and 5. I tried to add the
following lines to the program code below at the bottom but it crashed.

With cho.Chart
.SeriesCollection.Labels = True
.SeriesCategory.Labels = True
cho.Chart.ApplyDataLabels Type:=xlDataLabelsShow, LegendKey:=False
End With

How can I get the individual Category names to be displayed when I
use the following VBA code:

Sub OneChartPerSheet_v3()

Dim ws As Worksheet
Dim cho As ChartObject
Dim sRange As String
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim st As String

' change settings to suit
sRange = "$A$6:$F$37"
dTop = 45
dLeft = 460
dHeight = 350
dWidth = 320

For Each ws In ActiveWorkbook.Worksheets
Set cho = ws.ChartObjects.Add(dLeft, dTop, dWidth, dHeight)
'set chart type
cho.Chart.ChartType = xlXYScatter
With cho.Chart
.SetSourceData Source:=ws.Range(sRange), PlotBy:=xlColumns
' other chart formatting
.HasTitle = True
.ChartTitle.Characters.Text = ws.Range("A3")
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"FAM Fluorescence, RFUs"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"SR Fluorescence, RFUs"
.HasLegend = True
.Legend.Position = xlBottom
End With
Next
End Sub

 

[Jon Peltier]

Ted -

1. You could change your For Next to:

For i = 2 to 9 step 7
Set ws = ActiveWorkbook.Worksheets(i)
'' do the stuff
Next

2. Your data range is "$A$6:$F$37". Delete the contents of cell A6, so
column A is recognized as category labels and row 6 as series names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Ted Mifflin]

Jon:
Thanks for sending your solutions. Since I'm a VBA newbe, isn't
there a need to add a Dim statement for the Worksheets variable? maybe
something like:
Dim Worksheet(i) As Object

I also need to add a Dim statement for the counter i as well, right?

Appreciate your help on this VBA program.

Ted

 

[Jon Peltier]

Ted -

Here's what you need to declare:

Dim ws as Worksheet
Dim i as Integer

ActiveWorkbook.Worksheets(i) is the collection of worksheets in the
active workbook.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______