I am an accountant and I have around 500 different accounts that I'm trying
to keep a close eye on. What I would like to do is input the monthly data
that I would like to keep an eye on...data such as variance from budget,
variance from projection, etc. on one sheet and then put the charts on the
other sheet. That way in order to really analyze the data I can actually see
the trends. I can set up a chart and copy the chart...however, if all I do
is copy the chart and paste...both charts will point to the same source data.
When I copy and paste a chart I want the source data to be the data on the
next line. Going into each chart and changing the souce data for 500
accounts would take forever and a day. I've thought about creating a macro
(even though I'm not that great with macros) in order to accomplish this
feat...can anyone give me any suggestions????

Anyone's help will be greatly appreciated!!!!

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the size
and array of charts that suits you. Sample data:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include category names in first
row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol),
..Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow, iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
I am an accountant and I have around 500 different accounts that I'm trying
to keep a close eye on. What I would like to do is input the monthly data
that I would like to keep an eye on...data such as variance from budget,
variance from projection, etc. on one sheet and then put the charts on the
other sheet. That way in order to really analyze the data I can actually
see
the trends. I can set up a chart and copy the chart...however, if all I
do
is copy the chart and paste...both charts will point to the same source
data.
When I copy and paste a chart I want the source data to be the data on the
next line. Going into each chart and changing the souce data for 500
accounts would take forever and a day. I've thought about creating a
macro
(even though I'm not that great with macros) in order to accomplish this
feat...can anyone give me any suggestions????

Anyone's help will be greatly appreciated!!!!

Ok...I really don't know much about Macros. I know that you can record Excel
steps and the hit Ctrl X and Macros will reproduce whatever steps you
previously recorded. With that being said and now that I have revealed my
true ignorance concerning macros here's how I tried to apply your solution to
my problem:

I created a macro so that I could get to the code. In the macro that I
created all I did was 'hit' record and then type 1 and then 'hit' stop. I
then went to the code and deleted everything. I then copied your macro from
your response. The macro didn't work...I got a couple of error
messages...syntax, etc. I'm I completely stupid or did I simply do something
wrong???? Geeeeez, I've got to take a Visual Basic course!

"Jon Peltier" wrote:

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the size
and array of charts that suits you. Sample data:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include category names in first
row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol),
..Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow, iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
I am an accountant and I have around 500 different accounts that I'm trying
to keep a close eye on. What I would like to do is input the monthly data
that I would like to keep an eye on...data such as variance from budget,
variance from projection, etc. on one sheet and then put the charts on the
other sheet. That way in order to really analyze the data I can actually
see
the trends. I can set up a chart and copy the chart...however, if all I
do
is copy the chart and paste...both charts will point to the same source
data.
When I copy and paste a chart I want the source data to be the data on the
next line. Going into each chart and changing the souce data for 500
accounts would take forever and a day. I've thought about creating a
macro
(even though I'm not that great with macros) in order to accomplish this
feat...can anyone give me any suggestions????

Anyone's help will be greatly appreciated!!!!

Some of the lines of code wrapped in the message. Here's an updated post
which tries to correct the problem.

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the size
and array of charts that suits you. Sample data (note, message has text
wrapping; Jan through Dec data should be in a single row for each stock):

Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include categories in first row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol), _
.Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow, iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
Ok...I really don't know much about Macros. I know that you can record
Excel
steps and the hit Ctrl X and Macros will reproduce whatever steps you
previously recorded. With that being said and now that I have revealed my
true ignorance concerning macros here's how I tried to apply your solution
to
my problem:

I created a macro so that I could get to the code. In the macro that I
created all I did was 'hit' record and then type 1 and then 'hit' stop. I
then went to the code and deleted everything. I then copied your macro
from
your response. The macro didn't work...I got a couple of error
messages...syntax, etc. I'm I completely stupid or did I simply do
something
wrong???? Geeeeez, I've got to take a Visual Basic course!

"Jon Peltier" wrote:

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the
size
and array of charts that suits you. Sample data:

Jan Feb Mar Apr May Jun Jul Aug Sep
Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include category names in first
row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol),
..Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow,
iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
I am an accountant and I have around 500 different accounts that I'm
trying
to keep a close eye on. What I would like to do is input the monthly
data
that I would like to keep an eye on...data such as variance from
budget,
variance from projection, etc. on one sheet and then put the charts on
the
other sheet. That way in order to really analyze the data I can
actually
see
the trends. I can set up a chart and copy the chart...however, if all
I
do
is copy the chart and paste...both charts will point to the same source
data.
When I copy and paste a chart I want the source data to be the data on
the
next line. Going into each chart and changing the souce data for 500
accounts would take forever and a day. I've thought about creating a
macro
(even though I'm not that great with macros) in order to accomplish
this
feat...can anyone give me any suggestions????

Anyone's help will be greatly appreciated!!!!

Now I get an error message as follows:

Run-time error '1004':

Unable to get the Font property of the ChartTitle class

I went to your website and tried to look for this macro...but had no success.

I believe that your website is as follows:
http://peltiertech.com/Excel/Charts/ChartIndex.html

I this simply won't work...I really appreciate your help!

"Jon Peltier" wrote:

Some of the lines of code wrapped in the message. Here's an updated post
which tries to correct the problem.

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the size
and array of charts that suits you. Sample data (note, message has text
wrapping; Jan through Dec data should be in a single row for each stock):

Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include categories in first row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol), _
.Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow, iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
Ok...I really don't know much about Macros. I know that you can record
Excel
steps and the hit Ctrl X and Macros will reproduce whatever steps you
previously recorded. With that being said and now that I have revealed my
true ignorance concerning macros here's how I tried to apply your solution
to
my problem:

I created a macro so that I could get to the code. In the macro that I
created all I did was 'hit' record and then type 1 and then 'hit' stop. I
then went to the code and deleted everything. I then copied your macro
from
your response. The macro didn't work...I got a couple of error
messages...syntax, etc. I'm I completely stupid or did I simply do
something
wrong???? Geeeeez, I've got to take a Visual Basic course!

"Jon Peltier" wrote:

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the
size
and array of charts that suits you. Sample data:

Jan Feb Mar Apr May Jun Jul Aug Sep
Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include category names in first
row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol),
..Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow,
iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
I am an accountant and I have around 500 different accounts that I'm
trying
to keep a close eye on. What I would like to do is input the monthly
data
that I would like to keep an eye on...data such as variance from
budget,
variance from projection, etc. on one sheet and then put the charts on
the
other sheet. That way in order to really analyze the data I can
actually
see
the trends. I can set up a chart and copy the chart...however, if all
I
do
is copy the chart and paste...both charts will point to the same source
data.
When I copy and paste a chart I want the source data to be the data on
the
next line. Going into each chart and changing the souce data for 500
accounts would take forever and a day. I've thought about creating a
macro
(even though I'm not that great with macros) in order to accomplish
this
feat...can anyone give me any suggestions????

Anyone's help will be greatly appreciated!!!!

The macro isn't on the web site (I may post it, though, because it's a good
answer to a FAQ). I put it together for your question.

It only needs a minor adjustment, but to make sure you copy it properly, I
am posting the entire modified macro:


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include categories in first row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol), _
.Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow, iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
If .HasTitle Then
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
End If
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
Now I get an error message as follows:

Run-time error '1004':

Unable to get the Font property of the ChartTitle class

I went to your website and tried to look for this macro...but had no
success.

I believe that your website is as follows:
http://peltiertech.com/Excel/Charts/ChartIndex.html

I this simply won't work...I really appreciate your help!

"Jon Peltier" wrote:

Some of the lines of code wrapped in the message. Here's an updated post
which tries to correct the problem.

Here's a macro that will create an array of charts, one chart per row of
data. The data is on worksheet "Data", and you need a blank sheet named
"Charts". Adjust the constants in the top of the procedure to get the
size
and array of charts that suits you. Sample data (note, message has text
wrapping; Jan through Dec data should be in a single row for each stock):

Jan Feb Mar Apr May Jun Jul Aug Sep
Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include categories in first row
Const iFirstDataCol As Long = 1 ' include series names in first column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1, iFirstDataCol),
_
.Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow,
iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
Ok...I really don't know much about Macros. I know that you can record
Excel
steps and the hit Ctrl X and Macros will reproduce whatever steps you
previously recorded. With that being said and now that I have revealed
my
true ignorance concerning macros here's how I tried to apply your
solution
to
my problem:

I created a macro so that I could get to the code. In the macro that I
created all I did was 'hit' record and then type 1 and then 'hit' stop.
I
then went to the code and deleted everything. I then copied your macro
from
your response. The macro didn't work...I got a couple of error
messages...syntax, etc. I'm I completely stupid or did I simply do
something
wrong???? Geeeeez, I've got to take a Visual Basic course!

"Jon Peltier" wrote:

Here's a macro that will create an array of charts, one chart per row
of
data. The data is on worksheet "Data", and you need a blank sheet
named
"Charts". Adjust the constants in the top of the procedure to get the
size
and array of charts that suits you. Sample data:

Jan Feb Mar Apr May Jun Jul Aug Sep
Oct
Nov Dec
Stock A 9.12 10.13 11.27 12.14 12.85 13.57 14.35 16.00 18.28
16.95 18.77 18.77
Stock B 10.03 11.59 12.21 14.26 14.53 15.16 17.07 18.10 17.93
18.53 19.91 20.89
Stock C 10.79 11.34 11.71 14.48 13.30 16.82 15.98 17.96 18.57
20.06 20.16 22.62
Stock D 11.78 13.22 14.08 15.92 15.47 18.02 18.58 17.94 18.48
22.58 22.83 22.36
Stock E 11.68 13.91 14.22 15.80 16.38 17.75 19.60 19.96 22.84
21.37 21.69 24.45
Stock F 12.43 15.83 14.82 18.09 17.73 17.69 19.12 22.30 22.88
23.17 25.72 25.14


Sub MakeGridOfCharts()
' Jon Peltier 7/31/2007

' chart size
Const nRowsTall As Long = 12
Const nColsWide As Long = 6

' chart layout
Const nChartsPerRow As Long = 2

' data layout
Const iFirstDataRow As Long = 2 ' don't include category names in
first
row
Const iFirstDataCol As Long = 1 ' include series names in first
column
Const iLastDataCol As Long = 13

Dim iRow As Long
Dim chtob As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range

With Worksheets("Charts").Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
End With

For iRow = 2 To Worksheets("Data").Cells(2, 1).End(xlDown).Row

With Worksheets("Data")
Set rData = Union(.Range(.Cells(iFirstDataRow - 1,
iFirstDataCol),
..Cells(iFirstDataRow - 1, iLastDataCol)), _
.Range(.Cells(iRow, iFirstDataCol), .Cells(iRow,
iLastDataCol)))
End With

Set chtob = Worksheets("Charts").ChartObjects.Add( _
((iRow - iFirstDataRow) Mod nChartsPerRow) * dWidth, _
Int((iRow - 2) / nChartsPerRow) * dHeight, dWidth, dHeight)
With chtob.Chart
.SetSourceData Source:=rData
.ChartType = xlLineMarkers

'' here down is formatting
.HasLegend = False
.ChartArea.AutoScaleFont = False
With .ChartArea.Font
.Name = "Arial"
.Size = 8
End With
.ChartTitle.Font.Bold = True
.ChartTitle.Top = 0
With .PlotArea
.Left = 1
.Width = chtob.Chart.ChartArea.Width - 10
.Top = 9
.Height = chtob.Chart.ChartArea.Height - 9
.Interior.ColorIndex = 2
.Border.ColorIndex = 48
End With
.Axes(xlCategory).Border.ColorIndex = 48
.Axes(xlValue).Border.ColorIndex = 48
.Axes(xlValue).MajorGridlines.Border.ColorIndex = 15
End With
Next

End Sub


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


"David Haley" (donotspam) wrote in message
...
I am an accountant and I have around 500 different accounts that I'm
trying
to keep a close eye on. What I would like to do is input the
monthly
data
that I would like to keep an eye on...data such as variance from
budget,
variance from projection, etc. on one sheet and then put the charts
on
the
other sheet. That way in order to really analyze the data I can
actually
see
the trends. I can set up a chart and copy the chart...however, if
all
I
do
is copy the chart and paste...both charts will point to the same
source
data.
When I copy and paste a chart I want the source data to be the data
on
the
next line. Going into each chart and changing the souce data for
500
accounts would take forever and a day. I've thought about creating
a
macro
(even though I'm not that great with macros) in order to accomplish
this
feat...can anyone give me any suggestions????

Anyone's help will be greatly appreciated!!!!