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


-----------
Thanks for your time. I thought Excel macro programming would be easier
than this since this has been more of a challenge than I anticipated.

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 wrote:

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


-----------
Thanks for your time. I thought Excel macro programming would be easier
than this since this has been more of a challenge than I anticipated.

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 wrote:
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 wrote:

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


-----------
Thanks for your time. I thought Excel macro programming would be
easier than this since this has been more of a challenge than I
anticipated.

Ted -

Here's what you need to decla

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/
_______

Ted Mifflin wrote:
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 wrote:

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 wrote:

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


-----------
Thanks for your time. I thought Excel macro programming would be
easier than this since this has been more of a challenge than I
anticipated.