Select All charts in active sheet

[84wister]

I feel like this should be incredibly straightforward but it's evading me for whatever reason, and i'm getting irritated. I just want a simple one liner that will select all of the charts on the active sheet without having to concern myself with chart names, or grouping. Something functionally equivalent to clicking all of the charts while holding the crtl key. No, recording as a macro doesn't work so please don't tell me thats what i need to do. On my version of excel (i.e. 2007), recording a macro only gives me the last chart i selected. Here's what i've already tried

activesheet.chartobjects.activate
activesheet.chartobjects.select
activesheet.chartobjects.selectall
activesheet.chartobjects.select false

I work with large amounts of data spread cross numerous sheets so solutions where i have to define names, or chart groupings don't work well as names and numbers of charts are constantly fluctuating in what i'm doing. I just need a macro that will easily select all the charts on an active sheet so i can move them around easily, and quickly cut and paste to different sheets via ctrl-c and ctrl-v. Any help would be much appreciated, thanks.

 

[sheetspread]

charts.select

?

 

[84wister]

no that doesn't work charts.select yields a runtime 1004 error of "Method 'select' of object 'Sheets' failed"

ActiveSheet.Charts.Select fails with a runtime 438 error "Object doesn't support this property or method"

 

[sheetspread]

I'll keep looking for a 1 liner, but you probably need a loop (few liner)

 

[pgc01]

Hi

ActiveSheet.ChartObjects.Select

works for me

 

[sheetspread]

Found one:

ActiveSheet.ChartObjects.Select

 

[84wister]

Yea that's definitely not working. Has anyone tried running that line in excel 2007? Has anyone tried making a set of charts on one sheet, selecting all manually (i.e. ctrl + 'click'), copying/pasting them to a second sheet, and seeing if that one liner can successfully select all the charts when the second sheet is activated??

For me that line only selects the chart with the highest index out of the ChartObjects collection for the active sheet. That was actually one of the first things i tried.

 

[sheetspread]

I have Excel 2010 so perhaps 2007 is different. I did copy and paste three test charts to another sheet and ran the line, it selected them all as well. Do you have charts on multiple worksheets that you're selecting between?

 

[84wister]

not at all, for now i'm really only concerned with all the charts on whatever sheet i'm currently working on.

 

[sheetspread]

Did you try creating a few charts from simple data on a new workbook and running the code?