I used do a lot of macros before Visual Basic in Excel. Since th
arrival of the Visual Basic editor I have never had to edit
macro….until now. I just can’t find the name of what I am looking fo
to fix it so I’ll describe what I am doing and maybe somebody can hel
me.
I do a lot of spot charts in Excel. I am a Realtor so I will grap
large sets of data to compare several hundred to several thousand sale
on a scatter chart. I have a way I like to format them that is clea
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
spreadsheet. Before graphing a data set I just select it. It is at tha
point that I would like to start the macro and let it select all of m
preferences and so on.
The problem is that each time I start with a new data set on a ne
spreadsheet and the macro is looking for an absolute reference to th
sheet that I recorded the macro on. Unfortunately the spreadsheet
recorded on is no longer a valid stating place. If I rerun the macr
from the original worksheet everything works great. Once I close tha
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.
need that to be undetermined.
I like to select the Range before I run the macro and have the macr
just use that range.

If I could change the reference in this line I think this would wor
fine. Thank for any help you might be able to give me.
Jeff Whitbey


--
Message posted from http://www.ExcelForum.com