Tom,
I'm trying to do as you suggested, but can't get my code to work. The following was generated by the recorder:

Sheets(Array("Sales by Store Ch", "Sales (U) Ch", "Sales ($) Ch", "Chart Data", _
"Total by Store", "Total by Date")).Select
Sheets("Sales by Store Ch").Activate
Sheets(Array("Sales by Store Ch", "Sales (U) Ch", "Sales ($) Ch", "Chart Data", _
"Total by Store", "Total by Date")).Copy Befo=Workbooks( _
"KS Summary 6-11-2004.xls").Sheets(1)

I put this into my code, but when I run it I get 'Error 9: subscript out of range' and the debugger highlights the first "Sheets(Array...." line. I understand that has something to do with the size of the array, but I'm not sure how to fix it. I tried defining an array (with 6 elements) and assigning it to sheets(array.... but that didn't work. (sheets(array.... returns a variant?)

Need a little more help....Thanks.
Marcotte



"Tom Ogilvy" wrote:

copy the charts and the data (summary) sheets as a group, then the charts
will refer to the new location.

You can then go to the data sheets, do a copy of the cells and paste special
values to remove links created by the summary sheet(s).

--
Regards,
Tom Ogilvy

"Marcotte A" wrote in message
...
I have a large (12+ MB) inventory/sales tracking spreadsheet. I need to
distribute the summary pages of this spreadsheet on a regular bases (2-3x a
week probably). I have a macro that will copy paste special (values and
formats) the summary pages, but there are also charts I would like to copy.
Whenever I copy them, they always refer back to the original spreadsheet.

Is there a way to copy a chart and have it refer to the same ranges on the
sheet it is copied onto (these charts are embedded in worksheets, not on
their own sheet). Or do I have to write a macro that will create the charts
from scratch?