Finding and Selecting Charts in a Workbook

[peterhay]

Hi

I have a workbook that contains multiple charts on multiple tabs, each are name uniquely.

I want a way in VBA of being able to input a Chart name e.g. "Chart 1" and then having Excel automatically find the tab that the Chart is on, select that tab, and select the Chart. This is so I can then auto copy and paste the chart into Word (I already have the code working for that).

Any help on this would be much appreciated.

Thanks
Peter

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Dim Sh As Worksheet
    Dim Ch As ChartObject
    For Each Sh In ThisWorkbook.Worksheets
        For Each Ch In Sh.ChartObjects
            If Ch.Name = Target.Value Then
                Sh.Activate
                Ch.Select
                Exit Sub
            End If
        Next Ch
    Next Sh
End Sub

The code assumes the chart's name is in A1 on the active sheet. Adjust to suit.