I'm not entirely sure what the source of the chart title should be - your code isn't clear, and I'm
not sure that your code actually creates unique charts. It appears to just create the same chart
with the same data over and over again.

Anyway, try changing

..ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R

to this, to get the value from column B:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value

or this, to get the value from column C:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value


HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
If you have any ideas, please help!
I have the following code which works exactly as I need it to EXCEPT that it
does not change the data or chart titles. I am VERY new to VB and can't
figure out how to write the code to have the data loop. The code produces 12
pages with four
charts to a page but they are all titled the same with the same data. Any
suggestions?


Sub correctchartmaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheets(2).UsedRange.Rows.Count / 2) + 1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts").Range( _
"F8:F9,H8:H9,J8:J9,L8:L9,N8:N9"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4"
ActiveChart.SeriesCollection(2).XValues = _

"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10,GradesExamscharts!R2C12,Gra desExamscharts!R2C14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With


ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intRowStart = intRowStart + 8
intRowEnd = intRowEnd + 8
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

intSheetChartCount = 1

End If

Next intLoopCount

End Sub

Jackie,

What is the logic for picking up the data? You seem to want to step through by 8 rows at a time -
is that what you actually want?

Anyway, to get both the value from both columns 2 & 3, use this (which will put a space between):

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value & _
" " & Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value

Let me know...

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
OH MY GOD!!!!!!! THANK YOU THANK YOU THANK YOU!!!!!!! Now if I could just
bother you one more time. You were right, it does produce the same chart.
I thought if I could get the chart title to work, I could just use the same
syntax to amed the code to create the charts the same way. Will that work.
AND, how can I change your suggestion below to include BOTH, 2 & 3? (Right
now I just show 2). THANK YOU AGAIN. J-

"Bernie Deitrick" wrote:

I'm not entirely sure what the source of the chart title should be - your code isn't clear, and
I'm
not sure that your code actually creates unique charts. It appears to just create the same chart
with the same data over and over again.

Anyway, try changing

..ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R

to this, to get the value from column B:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value

or this, to get the value from column C:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value


HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
If you have any ideas, please help!
I have the following code which works exactly as I need it to EXCEPT that it
does not change the data or chart titles. I am VERY new to VB and can't
figure out how to write the code to have the data loop. The code produces 12
pages with four
charts to a page but they are all titled the same with the same data. Any
suggestions?


Sub correctchartmaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheets(2).UsedRange.Rows.Count / 2) + 1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts").Range( _
"F8:F9,H8:H9,J8:J9,L8:L9,N8:N9"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4"
ActiveChart.SeriesCollection(2).XValues = _

"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10,GradesExamscharts!R2C12,Gra desExamscharts!R2C14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With


ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intRowStart = intRowStart + 8
intRowEnd = intRowEnd + 8
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

intSheetChartCount = 1

End If

Next intLoopCount

End Sub

Jackie,

I'm unsure of your data table structure, but you can loop like in the code below. Copy everything
into a codemodule, and give it a try.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub NewChartMaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = 8 To Worksheets("GradesExamscharts") _
.Range("F65536").End(xlUp).Row Step 8


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts") _
.Cells(intLoopCount, 1).Range("F1:F2,H1:H2,J1:J2,L1:L2,N1:N2"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _
"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10," & _
"GradesExamscharts!R1C12,GradesExamscharts!R1C 14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R" & intLoopCount & "C4"
ActiveChart.SeriesCollection(2).XValues = _
"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10," & _
"GradesExamscharts!R2C12,GradesExamscharts!R2C 14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R" & intLoopCount + 1 & "C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intLoopCount , 2).Value &
_
" " & Worksheets("GradesExamscharts").Cells(intLoopCount , 3).Value
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
End With
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

SpaceOutCharts
intSheetChartCount = 1

End If

Next intLoopCount

End Sub

Sub SpaceOutCharts()
Dim chtCounter As Integer
Dim chtobj As ChartObject

chtCounter = 1
For Each chtobj In ActiveSheet.ChartObjects
chtobj.Top = 400 * (chtCounter - 1) + 100
chtCounter = chtCounter + 1
Next chtobj
End Sub




"Jackie" wrote in message
...
Yes, it is every 8th & 9th Row that I need the data from. Only from columns
F, H, J, L, AND N. You are wonderful! Thank you again and again!
"Bernie Deitrick" wrote:

Jackie,

What is the logic for picking up the data? You seem to want to step through by 8 rows at a
time -
is that what you actually want?

Anyway, to get both the value from both columns 2 & 3, use this (which will put a space between):

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value & _
" " & Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value

Let me know...

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
OH MY GOD!!!!!!! THANK YOU THANK YOU THANK YOU!!!!!!! Now if I could just
bother you one more time. You were right, it does produce the same chart.
I thought if I could get the chart title to work, I could just use the same
syntax to amed the code to create the charts the same way. Will that work.
AND, how can I change your suggestion below to include BOTH, 2 & 3? (Right
now I just show 2). THANK YOU AGAIN. J-

"Bernie Deitrick" wrote:

I'm not entirely sure what the source of the chart title should be - your code isn't clear,
and
I'm
not sure that your code actually creates unique charts. It appears to just create the same
chart
with the same data over and over again.

Anyway, try changing

..ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R

to this, to get the value from column B:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value

or this, to get the value from column C:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value


HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
If you have any ideas, please help!
I have the following code which works exactly as I need it to EXCEPT that it
does not change the data or chart titles. I am VERY new to VB and can't
figure out how to write the code to have the data loop. The code produces 12
pages with four
charts to a page but they are all titled the same with the same data. Any
suggestions?


Sub correctchartmaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheets(2).UsedRange.Rows.Count / 2) + 1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts").Range( _
"F8:F9,H8:H9,J8:J9,L8:L9,N8:N9"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4"
ActiveChart.SeriesCollection(2).XValues = _

"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10,GradesExamscharts!R2C12,Gra desExamscharts!R2C14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With


ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intRowStart = intRowStart + 8
intRowEnd = intRowEnd + 8
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

intSheetChartCount = 1

End If

Next intLoopCount

End Sub

My code has

Dim chtCounter As Integer

Not

Dim chtCounter As Interger

That extra r will throw things off ;-)

If you could, send me a workbook with your data, and indicate which rows are data, titles, etc.
It's hard to work on a dataset without the dataset....

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
Bernie,
Couple of kinks - This code seems to be returning the same data on every
chart, including the Chart Title. I think I copied every thing correctly,
but pasted below to see if you can see a mistake. I did change the
ActiveChart.SeriesCollection(1) & (2). Name back to what I had as that worked
better for me.
The second error I got was "compile error, user defined type not defined on
the line Dim chtCounter As Interger. Thoughts?

"Bernie Deitrick" wrote:

Jackie,

I'm unsure of your data table structure, but you can loop like in the code below. Copy
everything
into a codemodule, and give it a try.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub NewChartMaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = 8 To Worksheets("GradesExamscharts") _
.Range("F65536").End(xlUp).Row Step 8


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts") _
.Cells(intLoopCount, 1).Range("F1:F2,H1:H2,J1:J2,L1:L2,N1:N2"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _
"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10," & _
"GradesExamscharts!R1C12,GradesExamscharts!R1C 14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R" & intLoopCount & "C4"
ActiveChart.SeriesCollection(2).XValues = _
"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10," & _
"GradesExamscharts!R2C12,GradesExamscharts!R2C 14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R" & intLoopCount + 1 & "C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intLoopCount ,
2).Value &
_
" " & Worksheets("GradesExamscharts").Cells(intLoopCount , 3).Value
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
End With
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

SpaceOutCharts
intSheetChartCount = 1

End If

Next intLoopCount

End Sub

Sub SpaceOutCharts()
Dim chtCounter As Integer
Dim chtobj As ChartObject

chtCounter = 1
For Each chtobj In ActiveSheet.ChartObjects
chtobj.Top = 400 * (chtCounter - 1) + 100
chtCounter = chtCounter + 1
Next chtobj
End Sub




"Jackie" wrote in message
...
Yes, it is every 8th & 9th Row that I need the data from. Only from columns
F, H, J, L, AND N. You are wonderful! Thank you again and again!
"Bernie Deitrick" wrote:

Jackie,

What is the logic for picking up the data? You seem to want to step through by 8 rows at a
time -
is that what you actually want?

Anyway, to get both the value from both columns 2 & 3, use this (which will put a space
between):

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value & _
" " & Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value

Let me know...

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
OH MY GOD!!!!!!! THANK YOU THANK YOU THANK YOU!!!!!!! Now if I could just
bother you one more time. You were right, it does produce the same chart.
I thought if I could get the chart title to work, I could just use the same
syntax to amed the code to create the charts the same way. Will that work.
AND, how can I change your suggestion below to include BOTH, 2 & 3? (Right
now I just show 2). THANK YOU AGAIN. J-

"Bernie Deitrick" wrote:

I'm not entirely sure what the source of the chart title should be - your code isn't clear,
and
I'm
not sure that your code actually creates unique charts. It appears to just create the same
chart
with the same data over and over again.

Anyway, try changing

..ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R

to this, to get the value from column B:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value

or this, to get the value from column C:

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value


HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
If you have any ideas, please help!
I have the following code which works exactly as I need it to EXCEPT that it
does not change the data or chart titles. I am VERY new to VB and can't
figure out how to write the code to have the data loop. The code produces 12
pages with four
charts to a page but they are all titled the same with the same data. Any
suggestions?


Sub correctchartmaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheets(2).UsedRange.Rows.Count / 2) + 1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts").Range( _
"F8:F9,H8:H9,J8:J9,L8:L9,N8:N9"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4"
ActiveChart.SeriesCollection(2).XValues = _

"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10,GradesExamscharts!R2C12,Gra desExamscharts!R2C14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R9C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With


ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True

Jackie,

Which part isn't working right now?

To send the file, open the file, then choose File / Send to... Mail recipient (As Attachment)
and then send it to

deitbe at consumer dot org

with the obvious character substitutions and space deletions.

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
I couldn't be more stupid. I just re-ran the macro and changed to print
view, now I see, that is very helpful! J-

"Bernie Deitrick" wrote:

My code has

Dim chtCounter As Integer

Not

Dim chtCounter As Interger

That extra r will throw things off ;-)

If you could, send me a workbook with your data, and indicate which rows are data, titles, etc.
It's hard to work on a dataset without the dataset....

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
Bernie,
Couple of kinks - This code seems to be returning the same data on every
chart, including the Chart Title. I think I copied every thing correctly,
but pasted below to see if you can see a mistake. I did change the
ActiveChart.SeriesCollection(1) & (2). Name back to what I had as that worked
better for me.
The second error I got was "compile error, user defined type not defined on
the line Dim chtCounter As Interger. Thoughts?

"Bernie Deitrick" wrote:

Jackie,

I'm unsure of your data table structure, but you can loop like in the code below. Copy
everything
into a codemodule, and give it a try.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub NewChartMaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = 8 To Worksheets("GradesExamscharts") _
.Range("F65536").End(xlUp).Row Step 8


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts") _
.Cells(intLoopCount, 1).Range("F1:F2,H1:H2,J1:J2,L1:L2,N1:N2"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _
"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10," & _
"GradesExamscharts!R1C12,GradesExamscharts!R1C 14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R" & intLoopCount & "C4"
ActiveChart.SeriesCollection(2).XValues = _
"=(GradesExamscharts!R2C6,GradesExamscharts!R2C8,G radesExamscharts!R2C10," & _
"GradesExamscharts!R2C12,GradesExamscharts!R2C 14)"
ActiveChart.SeriesCollection(2).Name = "=GradesExamscharts!R" & intLoopCount + 1 & "C4"
ActiveChart.Location Whe=xlLocationAsObject, Name:=strSheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intLoopCount ,
2).Value &
_
" " & Worksheets("GradesExamscharts").Cells(intLoopCount , 3).Value
.ChartTitle.Select
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlLeft
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.Size = 7
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
End With
Selection.Font.Bold = True
ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnit = 0.04
.MajorUnit = 0.2
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'Increment counters
intSheetChartCount = intSheetChartCount + 1

'Reset sheet chart counter to 1 if it goes beyond 4
If intSheetChartCount = 5 Then

SpaceOutCharts
intSheetChartCount = 1

End If

Next intLoopCount

End Sub

Sub SpaceOutCharts()
Dim chtCounter As Integer
Dim chtobj As ChartObject

chtCounter = 1
For Each chtobj In ActiveSheet.ChartObjects
chtobj.Top = 400 * (chtCounter - 1) + 100
chtCounter = chtCounter + 1
Next chtobj
End Sub




"Jackie" wrote in message
...
Yes, it is every 8th & 9th Row that I need the data from. Only from columns
F, H, J, L, AND N. You are wonderful! Thank you again and again!
"Bernie Deitrick" wrote:

Jackie,

What is the logic for picking up the data? You seem to want to step through by 8 rows at a
time -
is that what you actually want?

Anyway, to get both the value from both columns 2 & 3, use this (which will put a space
between):

..ChartTitle.Characters.Text = Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value &
_
" " & Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value

Let me know...

HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
OH MY GOD!!!!!!! THANK YOU THANK YOU THANK YOU!!!!!!! Now if I could just
bother you one more time. You were right, it does produce the same chart.
I thought if I could get the chart title to work, I could just use the same
syntax to amed the code to create the charts the same way. Will that work.
AND, how can I change your suggestion below to include BOTH, 2 & 3? (Right
now I just show 2). THANK YOU AGAIN. J-

"Bernie Deitrick" wrote:

I'm not entirely sure what the source of the chart title should be - your code isn't
clear,
and
I'm
not sure that your code actually creates unique charts. It appears to just create the
same
chart
with the same data over and over again.

Anyway, try changing

..ChartTitle.Characters.Text = 'GradesExamscharts'!R & intRowStart
C2:R & intRowStart C3:R

to this, to get the value from column B:

..ChartTitle.Characters.Text =
Worksheets("GradesExamscharts").Cells(intRowStart, 2).Value

or this, to get the value from column C:

..ChartTitle.Characters.Text =
Worksheets("GradesExamscharts").Cells(intRowStart, 3).Value


HTH,
Bernie
MS Excel MVP


"Jackie" wrote in message
...
If you have any ideas, please help!
I have the following code which works exactly as I need it to EXCEPT that it
does not change the data or chart titles. I am VERY new to VB and can't
figure out how to write the code to have the data loop. The code produces 12
pages with four
charts to a page but they are all titled the same with the same data. Any
suggestions?


Sub correctchartmaker()
'
' correctchartmaker Macro
' Macro recorded 12/15/2006 by Jackie
'

'Declare Variables
Dim intLoopCount As Integer 'Counter for loop
Dim intRowStart As Integer 'Row number to start at for first chart
Dim intRowEnd As Integer 'Row number to end at for first chart
Dim intSheetChartCount As Integer 'Count of charts on a sheet
Dim intSheetCount As Integer 'Count of sheets
Dim strSheetName As String 'Name of sheet


'Initialize Variables
intLoopCount = 8 'Start in row 8 forloop
intRowStart = 8 'Start in row 8
intRowEnd = 9 'End in row 9
intSheetChartCount = 1 'First chart on sheet
intSheetCount = 1 'First sheet of charts


'Loop to create charts until no data is found
For intLoopCount = intRowStart To (Sheets(2).UsedRange.Rows.Count / 2) + 1


'Check if new sheet needs to be created
If intSheetChartCount = 1 Then

'Create a blank sheet
Worksheets.Add.Name = "Page" & intSheetCount

'Name Sheet
strSheetName = "Page" & intSheetCount

'Increment counter
intSheetCount = intSheetCount + 1

End If

'Create a blank chart
Charts.Add

'Format chart as Clustered Column
ActiveChart.ChartType = xlColumnClustered

'Set data source and data range
ActiveChart.SetSourceData Source:=Sheets("GradesExamscharts").Range( _
"F8:F9,H8:H9,J8:J9,L8:L9,N8:N9"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _

"=(GradesExamscharts!R1C6,GradesExamscharts!R1C8,G radesExamscharts!R1C10,GradesExamscharts!R1C12,Gra desExamscharts!R1C14)"
ActiveChart.SeriesCollection(1).Name = "=GradesExamscharts!R8C4"
ActiveChart.SeriesCollection(2).XValues = _