Chart sheet copy and pasted to power point

Question

Hello I am trying to get many chart sheets copied and pasted into PowerPoint with each chart sheet having it's own slide.

This code will only take charts in a work sheet not chart sheets.

I found most of the code online and modified it so that it paste the chart as a png image into the slide. How can I change it so I can loop through all the chart sheets and get them to past into excel. Also I would like to start with the left chart and loop from left to right.

 Sub ExportChartsToPowerPoint_MultipleWorksheets()



    ' OVERVIEW:

    ' This script will loop through all the worksheets in the Active Workbook

    ' and copy all the Charts to a new PowerPoint presentation that we create.

    ' Each chart will get their own individual slide and will be placed in the center of it.



    'Declare PowerPoint Variables

    Dim PPTApp As PowerPoint.Application

    Dim pptPres As PowerPoint.Presentation

    Dim PPTSlide As PowerPoint.Slide

    Dim PPTShape As PowerPoint.Shape

    Dim SldIndex As Integer



    'Declare Excel Variables

    Dim Chrt As ChartObject
    Dim Chart As Chart
    Dim WrkSht As Worksheet


    'Create new PowerPoint Application & make it visible.

    Set PPTApp = New PowerPoint.Application

        PPTApp.Visible = True


    'Create new presentation in the PowerPoint application.

    Set pptPres = PPTApp.Presentations.Add


    'Create an index handler for slide creation.

    SldIndex = 1


    For Each WrkSht In Worksheets


        'Loop through all the CHARTOBJECTS in the ACTIVESHEET.

        For Each Chrt In WrkSht.ChartObjects



            'Copy the Chart

            Chrt.Copy


            'Create a new slide in the Presentation, set the layout to blank, and paste chart on to the newly added slide.

            Set PPTSlide = pptPres.Slides.Add(SldIndex, ppLayoutBlank)

                PPTSlide.Shapes.PasteSpecial DataType:=ppPastePNG



            'Increment index so that way we paste the next chart on the new slide that is added.

            SldIndex = SldIndex + 1



        Next Chrt


      Next WrkSht
End Sub

What problem are you having with this code? If there is an error message, on which line does it appear? — John Korchok
It will not copy and paste the charts in chart sheets.It only works on charts in worksheets. — G_EXL_snake

JoshL JoshL · Accepted Answer · 2020-07-10T16:29:27

I recently went through this copying and pasting charts from chart sheets into a word doc. I ended up having to go about it in a round about way but the only way I found to copy/paste charts from chart sheets was to do..

Charts("Name Of Chart Sheet").Select
Selection.Copy

Then you can go ahead with pasting it wherever you were looking. Otherwise it would make a new workbook with a new chart sheet when I copied it.

Chart sheet copy and pasted to power point

1 Answers