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?

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?

Tom,
Nevermind...figured it out. Right before this code I have a sub that adds a new workbook. Since the new (empty) workbook was now active, the Sheets(array... below wasn't finding the sheet names. I added a 'ThisWorkbook.Activate' just before the code below and it works fine. Thanks.

"Marcotte A" wrote:

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?

I use the first macro (Tester1) to reproduce your sheet names:

Sub Tester1()
varr = Array("Sales by Store Ch", "Sales (U) Ch", "Sales ($) Ch", "Chart
Data", _
"Total by Store", "Total by Date")

For i = LBound(varr) To UBound(varr)
Sheets(i + 1).Name = varr(i)
Next

End Sub

Then this macro ran fine:

Sub AAA()
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)
End Sub

I suspect you had KS Summary 6-11-2004.xls as the activeworkbook when you
ran your macro. It must be the workbook containing the sheets that you want
to copy.

--
Regards,
Tom Ogilvy


"Marcotte A" wrote in message
...
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?