VBA to list location of all charts on 2 worksheets

[Access Beginner]

Hello,

I have a workbook that has a lot of charts (over 300 on 2 sheets).
What I am after if possible, is to list each chart on each sheet and it's location (cell reference).

Sheet one is called: Chart_Tables

Sheet two is called: Remote_Chart_Tables

Worksheet name where they are to be listed: ChartLocations
Chart_Tables to be listed from B2 in ChartLocations
Remote_Chart_Tables to be listed from D2 in ChartLocations

 

[Peter_SSs]

See if this is the sort of thing you are after.

Sub Chart_Locations()
  Dim ch As ChartObject
  Dim itm As Variant
  Dim ws As Worksheet
  Dim rFirst As Range
  Dim oSet As Long
  
  Const sData As String = "Chart_Tables#B2|Remote_Chart_Tables#D2"
  
  For Each itm In Split(sData, "|")
    oSet = 0
    Set ws = Sheets(Split(itm, "#")(0))
    Set rFirst = Sheets("ChartLocations").Range(Split(itm, "#")(1))
    
    For Each ch In ws.ChartObjects
      rFirst.Offset(oSet).Value = ch.TopLeftCell.Address(0, 0)
      oSet = oSet + 1
    Next ch
  Next itm
End Sub

 

[Yongle]

Or this
(sheet name "ChartLocations" does not include an underscore as per your notes)

Sub ListCharts()
    Dim RCT As Worksheet, CT As Worksheet, CL As Worksheet, chrt As ChartObject, cel As Range
    Set RCT = Sheets("Remote_Chart_Tables")
    Set CT = Sheets("Chart_Tables")
    Set CL = Sheets("ChartLocations")

'clear old values (allows repeated runs)
    CL.Range("B1:F" & Rows.Count).ClearContents
    Range("B1").Resize(, 4).Value = Split("Chart,Addr,Remote,Addr", ",")
'create list of chart locations
    For Each chrt In CT.ChartObjects
        Set cel = CL.Cells(Rows.Count, "B").End(xlUp).Offset(1)
        cel = chrt.Name
        cel.Offset(, 1) = chrt.TopLeftCell.Address(0, 0)
    Next
    For Each chrt In RCT.ChartObjects
        Set cel = CL.Cells(Rows.Count, "D").End(xlUp).Offset(1)
        cel = chrt.Name
        cel.Offset(, 1) = chrt.TopLeftCell.Address(0, 0)
    Next
End Sub

 

[Access Beginner]

Thanks Peter, worked like a charm

 

[Access Beginner]

Thanks Yongle, worked perfectly and even though I didn't stipulate for the chart name to be included ( and I did want that) your code did the trick.

 

[Peter_SSs]

Thanks Peter, worked like a charm

You're welcome, but yes, I missed the part about the chart names.
However, my overall method would be useful if there might later be other worksheets with charts to also process as all you would need to do is to add to the 'Const' line to list the sheet names and result starting cells.