Copying Charts While Changing Source Data

[David Haley]

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!!!!

 

[Jon Peltier]

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

 

[David Haley]

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]

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

 

[David Haley]

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]

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

 

[David Haley]

Thanks the Macro is GREAT!!! I really do appreciate your help. However, I
do have another question. What if I want to modify the chart? Say I want a
different chart or I want to add a second line of data to display in the
chart? Is there a way that I can take this macro and...when I make
changes...somehow change all the other charts? Or, would it be possible for
me to create the exact chart that I want (recording a new macro while I'm
creating the chart) and then drop it into the macro that you've created?

It looks as though I will have to change the logic in order to change the
type of chart etc. Am I right?

I thought that this macro would basically copy the first chart that I create
for X number of lines of data.

Basically what I would like to do is create the chart that I want and then
engage the macro and have it copy the chart for X number of lines...I hope
I'm making sense.

I've already been tinkering with the logic in order to better understand
what changes what.

Any iteas?

 

[Jon Peltier]

What you want is a bit more complicated, but much more flexible. My way just
creates all of the charts the same way without easy customization. This is
something I'll have to consider when I have a free moment.

- Jon

 

[David Haley]

First let me say thanks for you help. The macro that you created will be
useful it's just not exactly what I was looking for...either way, all your
help is above and beyond the call of duty to help us ignorant accountants
out!! I so obsessed with this idea that I've began reading all I can about
macros and visual basic! Anyway, in my mind what I would like is to be able
to create a macro that would put a chart on a different sheet in the upper
left hand corner...as your macro did. The initial chart would pull data from
line 1. The next step of the macro would be to copy the inital
chart...except pull data from the next line. So, it seems to me that the
trick is to program the macro to copy insert a basic chart, copy that chart,
and the pull data from line 2 rather than line 1. The inital chart could be
a basic chart because since the macro is copying the chart itself and then
pulling data from a different row on the data sheet...whenever I change the
formatting or chart style in the initial chart it would updata or change all
of the other charts. I believe that what your macro accomplished was to
insert a basic chart in position X and then pull data from line X...then
repeat until there was no additional data to pull from. I've been looking at
the logic for a basic macro that records the creation of a chart. In my mind
I think that it would be great if I could alter that initial macro to move
the initial chart to a location where I want it then copy that macro and pull
data from the next line and stop when there is no additional data. Well, I
know I've probably repeated myself a couple of times and you're probably
thinking....OKAY...I get it!!!

I'm so interested in this macro because I may be taking a different job
which would be back in the finance area (a Controller's position) and this
macro would really help me out when it comes to analyzing financial items
such as current, budget, and projection. It would really help me see the
trends...so, if you come up with something...please let me know!!! When I
win the lottery I'll give you 50%!!!!!

Thanks--David Haley
(e-mail address removed)