Leith,
Thanks for the response -- I'm anxious to try it when I'm back in the
office. Have a great holiday!
Dave
"Leith Ross" wrote:
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
--------------------
Sincerely,
Leith Ross
--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557579