Copy Chart as sheet into Powerpoint

[mhessnm]

Hello, I cannot seem to make this work. I have a number of charts which I moved onto their own worksheets. I have run across a lot of help writing VBA to take chart objects off worksheets and pasting them onto separate slides in PowerPoint. What I cannot seem to do is find a way to take charts that are their own sheets and paste them into PowerPoint.

Is there a difference between chart objects that sit on a worksheet (i.e. you have one or more charts sitting on a worksheet with your data), and chart objects that have been moved onto their own sheets using right click, Move Chart, New Sheet? And if so, how do you reference them in VBA. I thought that this code, which I learned on a YouTube video, would do the trick. Instead, it copied one chart that I didn't even know existed, but didn't take any of the charts that I had moved onto their own sheets. What can I do to get those charts copied over (and save me a lot of clicking)?

Thank you in advance for your help.

Sub ExportChartsToPowerpoint_SingleWorkbook()

'Declare Powerpoint Object Variables
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSlide As PowerPoint.Slide
Dim SldIndex As Integer

'Declare Excel Object Variables
Dim Chrt As ChartObject
Dim WrkSht As Worksheet

'Create a New Instance of Powerpoint
Set PPTApp = New PowerPoint.Application
PPTApp.Visible = True

'Create a new presentation within the application
Set PPTPres = PPTApp.Presentations.Add

'Create an index handler for slide creation
SldIndex = 1

'Loop through all of the worksheets in the active workbook
For Each WrkSht In Worksheets


'Loop through all the charts on the Activesheet
For Each Chrt In WrkSht.ChartObjects

'Copy the chart
Chrt.Copy

'Create a new slide, set the layout to blank, and paste the chart on the slide
Set PPTSlide = PPTPres.Slides.Add(SldIndex, ppLayoutBlank)
PPTSlide.Shapes.Paste

'Increment the slide index
SldIndex = SldIndex + 1

Next Chrt

Next WrkSht

End Sub

 

[Domenic]

You'll need to loop through each Chart sheet within the Charts collection...

    Dim chrt As Chart
    
    For Each chrt In ActiveWorkbook.Charts
    
        chrt.ChartArea.Copy
        
        'your code to create a new slide, set the layout to blank, and paste the chart on the slide
        '
        '
    
    Next chrt

Hope this helps!

 

[mhessnm]

Thank you so much, Domenic! It worked perfectly. Also helped me understand the differences referring to charts on a sheet versus charts in their own sheet. I appreciate the help!

 

[Domenic]

You're very welcome, glad I could help!

Cheers!