Hi Jeff,
Change Sheets("BERKLEY") to ActiveSheet if the selected range will
always be the same. If the selected range will be different then it may be
better to replace Sheets("BERKLEY").Range("A1:B873") with Selection instead.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Jeff Whitbey " wrote in
message ...
I used do a lot of macros before Visual Basic in Excel. Since the
arrival of the Visual Basic editor I have never had to edit a
macro..until now. I just can't find the name of what I am looking for
to fix it so I'll describe what I am doing and maybe somebody can help
me.
I do a lot of spot charts in Excel. I am a Realtor so I will graph
large sets of data to compare several hundred to several thousand sales
on a scatter chart. I have a way I like to format them that is clear
for others to read that takes about 40 steps so I want to do a macro.
I start with new data each time just setting there selected in a
spreadsheet. Before graphing a data set I just select it. It is at that
point that I would like to start the macro and let it select all of my
preferences and so on.
The problem is that each time I start with a new data set on a new
spreadsheet and the macro is looking for an absolute reference to the
sheet that I recorded the macro on. Unfortunately the spreadsheet I
recorded on is no longer a valid stating place. If I rerun the macro
from the original worksheet everything works great. Once I close that
sheet and open a new data set I get an error.
It seem that I need to edit the line to not have any reference in it.
Kind of the opposite of an absolute reference.
Error is: runtime error 9 - subscript out of range
ActiveChart.SetSourceData Source:=Sheets("BERKLEY").Range("A1:B873"),
PlotBy _
:=xlColumns
The above line is copied out of the Visual basic editor.
The source "BERKLEY" was the name of the worksheet I recorded in. I
need that to be undetermined.
I like to select the Range before I run the macro and have the macro
just use that range.
If I could change the reference in this line I think this would work
fine. Thank for any help you might be able to give me.
Jeff Whitbey
---
Message posted from http://www.ExcelForum.com/