Copy Chart as sheet into Powerpoint

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,725
You'll need to loop through each Chart sheet within the Charts collection...

VBA Code:
    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!
 
Solution

mhessnm

New Member
Joined
Apr 12, 2019
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,141,575
Messages
5,707,173
Members
421,495
Latest member
jono_oh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top