Auto-increment data source cell references when copying charts

[Guest]

Hello.

I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.

I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet -- so Excel doesn't increment the references when
they get copied.

But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?

Thanks in advance -- Dave

 

[Leith Ross]

Hello Dave,

To use this macro you will first need to add a VBA Module to your
Workbook and then paste in the macro code. You can manually run the
macro using the Macro Dialog dialog in Excel. Press ALT+F8 to activate
the dialog. Select the macro and press ENTER or click "Run" to execute
it. You can assign a short cut key to the macro using by clicking the
"Options..." button in the dialog box.

You didn't maention what your intial range address was. I have it set
for "A1:A25". You can change this by editing the macro code before you
copy it. Find "Addx = "$A$1:$A$25" in the macro. Set the range address
to what you are using. The variable RowOffset adds 50 to the first
address of the range. The start of the next range address would be
$A$51 and continue to $A$100.

Adding the macro to the Workbook:
1) Copy the macro code to the clipboard using CTRL+C.
2) Open the Workbook in Excel.
3) Press ALT+F11 to open the VBA Editor.
4) Press ALT + I to activate the Insert Menu.
5) Press M to add a module to the workbook.
6) Press CTRL+V to paste the macro code in.
7) Press Ctrl+s to save the macro.
8) Press ALT+Q to close the VBA Editor and return to Excel.


Code:
--------------------

Sub AutoSetChartRange()

Dim Addx As String
Dim Chrt As Chart
Dim R As Long
Dim RowOffset As Long
Dim SrcRng As Range

Addx = "A1:A25"
RowOffset = 50

With ActiveSheet
'R = Last Chart number in collection
R = .ChartObjects.Count
'Set object variable equal to last chart
Set Chrt = .ChartObjects(R).Chart
'Set range object variable equal to first chart's source range
Set SrcRng = .Range(Addx)
'Calculate offset from original source range
R = (R * RowOffset) - RowOffset
'Set last chart's source range
Chrt.SetSourceData Source:=SrcRng.Offset(R, 0)
End With

End Sub

 

[Guest]

Leith,

Thanks for the response -- I'm anxious to try it when I'm back in the
office. Have a great holiday!

Dave