Brad77
09-19-2008, 09:25 AM
Hi,
I've got a spreadsheet with several sheets of data, each has a button to view a chart (on a seperate sheet), the button only does
Sheets("Chart1").Activate
The Chart gets its data values from
='SpreadsheetName.xls'!ValuesIn1
='SpreadsheetName.xls'!ValuesOut1
These are Ranges, added using Insert->Name->Define
e.g.
=OFFSET('SpreadsheetName.xls'!XAxisLabels1,0,1)
At the moment there are 5 charts each linked from the 1st 5 sheets, but now i need to add charts for the other sheets (21 in total).
Instead of adding a new chart sheet for each sheet, I'd like to have just 1 chart, that references ValuesIn and ValuesOut, (instead of charts ref'ing ValuesIn1, ValuesIn2 etc)
Is it possible to make the button on each sheet copy the range i require over to the range that is being used by the chart?
I've been playing around with
Range("ValuesIn").Value = Range("ValuesIn1")
or
Range("ValuesIn2").Cut
Range("ValuesIn").Insert
or
Sheets("WholeZone").Range("ValuesIn2").Copy (Sheets("WholeZone").Range("ValuesIn"))
But it seems I have no idea what I'm doing and keep getting the following error:
Run-time error '1004':
Application-defined or object-defined error
If anyone can point out how I should be doing this, or point me to a nice tutorial, that would be very much appreciated.
Thanks,
Brad
I've got a spreadsheet with several sheets of data, each has a button to view a chart (on a seperate sheet), the button only does
Sheets("Chart1").Activate
The Chart gets its data values from
='SpreadsheetName.xls'!ValuesIn1
='SpreadsheetName.xls'!ValuesOut1
These are Ranges, added using Insert->Name->Define
e.g.
=OFFSET('SpreadsheetName.xls'!XAxisLabels1,0,1)
At the moment there are 5 charts each linked from the 1st 5 sheets, but now i need to add charts for the other sheets (21 in total).
Instead of adding a new chart sheet for each sheet, I'd like to have just 1 chart, that references ValuesIn and ValuesOut, (instead of charts ref'ing ValuesIn1, ValuesIn2 etc)
Is it possible to make the button on each sheet copy the range i require over to the range that is being used by the chart?
I've been playing around with
Range("ValuesIn").Value = Range("ValuesIn1")
or
Range("ValuesIn2").Cut
Range("ValuesIn").Insert
or
Sheets("WholeZone").Range("ValuesIn2").Copy (Sheets("WholeZone").Range("ValuesIn"))
But it seems I have no idea what I'm doing and keep getting the following error:
Run-time error '1004':
Application-defined or object-defined error
If anyone can point out how I should be doing this, or point me to a nice tutorial, that would be very much appreciated.
Thanks,
Brad