Copy a range ?

[Brad77]

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

[MaximS]

try:




Range("ValuesIn").Value = Range("ValuesIn1").Value




or


Sheets("WholeZone").Range("ValuesIn2").Copy Destination:=Sheets("WholeZone").Range("ValuesIn")

[Brad77]

Thank you very much for the quick reply MaximS.

The first suggestion gave me an error "Method 'Range' of object '_worksheet' failed.
So I tried the 2nd suggestion, as it specifies the worksheets.
This worked fine.

I had to change the Sheet name of the source part to that of the sheet the button is on, which surprises me, I thought that 'Defines' were avaliable to all sheets.

Thanks again,
Brad.