Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple row selecting | Excel Worksheet Functions | |||
How do I keep from selecting multiple cells | Excel Worksheet Functions | |||
Charts - How to have multiple charts share a legend. | Charts and Charting in Excel | |||
Selecting Embedded charts in Active Sheets programmatically | Charts and Charting in Excel | |||
Selecting lots of charts | Excel Discussion (Misc queries) |