selecting multiple charts
 

Do you want to select all the chart sheets? Try this:

ActiveWorkbook.Charts.Select

To select only some of them, you need to select an array of chart names:

ActiveWorkbook.Charts(Array("Chart1", "Chart2")).Select

I retooled your macro slightly and it worked fine (your original one did
not, and ten seconds of deep thought couldn't shed any light).

Sub macro2()
Dim x As Long
Dim chrts() As String
ReDim chrts(1 To ActiveWorkbook.Charts.Count)
For x = 1 To ActiveWorkbook.Charts.Count
chrts(x) = ActiveWorkbook.Charts(x).Name
Next x
ActiveWorkbook.Charts(chrts).Select
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

mrmac wrote:
I am trying to programmatically copy x number of charts
from one workbook to another. I have tried replicating a
recorded macro, using a string to represent the names of
the tabs in the workbook. It comes up with "Subscript Out
of Range" error. What's the right way to do this?

Sub macro1()
chrts = Chr(34)
For x = 1 To Charts.Count
chrts = chrts & Charts(x).Name & Chr(34) & Chr(44) & Chr
(32) & Chr(34)
Next x
chrts = Left(chrts, Len(chrts) - 3)
Sheets(Array(chrts)).Select
End Sub

Thanks for any help

selecting multiple charts
 

Thank you Jon. It is a lot simpler than I thought.

FYI. The deal is to copy all charts from a wb to a new
wb, then to copy a subset of this to a new wb. Then close
these wbs, delete most of the charts from the original wb
and create new charts from different data. This is done
12 times in a loop and the number of charts created in
each loop is variable. This is the piece I was missing.
Thanks for your timely help.

-----Original Message-----
Do you want to select all the chart sheets? Try this:

ActiveWorkbook.Charts.Select

To select only some of them, you need to select an array
of chart names:

ActiveWorkbook.Charts(Array("Chart1", "Chart2")).Select

I retooled your macro slightly and it worked fine (your
original one did
not, and ten seconds of deep thought couldn't shed any
light).

Sub macro2()
Dim x As Long
Dim chrts() As String
ReDim chrts(1 To ActiveWorkbook.Charts.Count)
For x = 1 To ActiveWorkbook.Charts.Count
chrts(x) = ActiveWorkbook.Charts(x).Name
Next x
ActiveWorkbook.Charts(chrts).Select
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

mrmac wrote:
I am trying to programmatically copy x number of
charts
from one workbook to another. I have tried replicating
a
recorded macro, using a string to represent the names
of
the tabs in the workbook. It comes up with "Subscript
Out
of Range" error. What's the right way to do this?

Sub macro1()
chrts = Chr(34)
For x = 1 To Charts.Count
chrts = chrts & Charts(x).Name & Chr(34) & Chr(44) &
Chr
(32) & Chr(34)
Next x
chrts = Left(chrts, Len(chrts) - 3)
Sheets(Array(chrts)).Select
End Sub

Thanks for any help


.

selecting multiple charts
 

1. Is there a simple way to copy a variable number of charts
embeded in a sheet to another sheet (where every time we have a
different number
of Charts in one sheet. The charts are not as sheets but embeded in a
sheet).

2. Is there a way to convert the charts from embeded in a sheet to
Chart sheets)

In the example below, I have 8 charts. :

=========
Dim sh as Worksheet
Dim sh1 as Worksheet
Sh.Shapes.Range(Array("Chart 1", "Chart 2", "Chart 3", "Chart 4" _
, "Chart 5", "Chart 6", "Chart 7", "Chart 8")).Select

Selection.Copy

Sh1.Paste
===========

In other cases it could be different (e.g. 6, 7, 13, etc.) So I am
looking for a way to use a vriable or chart index to represent the
charts in the array

"mrmac" wrote in message ...
Thank you Jon. It is a lot simpler than I thought.

FYI. The deal is to copy all charts from a wb to a new
wb, then to copy a subset of this to a new wb. Then close
these wbs, delete most of the charts from the original wb
and create new charts from different data. This is done
12 times in a loop and the number of charts created in
each loop is variable. This is the piece I was missing.
Thanks for your timely help.

-----Original Message-----
Do you want to select all the chart sheets? Try this:

ActiveWorkbook.Charts.Select

To select only some of them, you need to select an array
of chart names:

ActiveWorkbook.Charts(Array("Chart1", "Chart2")).Select

I retooled your macro slightly and it worked fine (your
original one did
not, and ten seconds of deep thought couldn't shed any
light).

Sub macro2()
Dim x As Long
Dim chrts() As String
ReDim chrts(1 To ActiveWorkbook.Charts.Count)
For x = 1 To ActiveWorkbook.Charts.Count
chrts(x) = ActiveWorkbook.Charts(x).Name
Next x
ActiveWorkbook.Charts(chrts).Select
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

mrmac wrote:
I am trying to programmatically copy x number of
charts
from one workbook to another. I have tried replicating
a
recorded macro, using a string to represent the names
of
the tabs in the workbook. It comes up with "Subscript
Out
of Range" error. What's the right way to do this?

Sub macro1()
chrts = Chr(34)
For x = 1 To Charts.Count
chrts = chrts & Charts(x).Name & Chr(34) & Chr(44) &
Chr
(32) & Chr(34)
Next x
chrts = Left(chrts, Len(chrts) - 3)
Sheets(Array(chrts)).Select
End Sub

Thanks for any help


.