Copy/Paste Charts To PowerPoint Excel VBA Macro

In this post we learn how to copy/paste charts from Excel to PowerPoint using VBA macros. The macro copies all embedded charts in a worksheet, and pastes each of them in a separate slide in PowerPoint as a picture.

 

Macro code:

 

  Sub CopyPasteChartsToPowerPoint()

      Dim newPP As Object, oChart As ChartObject

      If ActiveSheet.ChartObjects.Count > 0 Then

          Set newPP = CreateObject("PowerPoint.Application")

          newPP.Presentations.Add

          For Each oChart In ActiveSheet.ChartObjects

              oChart.CopyPicture

              With newPP

                   .ActivePresentation.Slides.Add .ActivePresentation.Slides.Count + 1, 12

                   .ActiveWindow.View.GotoSlide .ActivePresentation.Slides.Count

                   .ActiveWindow.View.Paste

              End With

          Next

      End If

  End Sub

 

Macro explained:

• First we declare two object variables. The variable newPP is used to store the PowerPoint application object. The variable oChart is used to reference each embedded chart in a worksheet. It is declared as a ChartObject object variable.
• Then we check if there are any embedded charts (ChartObjects) in the active worksheet to be copied to PowerPoint. Otherwise, the rest of the code will not run. The charts have been previously resized to have the same size and fit the PowerPoint slide adequately. Check this other post to Resize All Charts In Worksheet.
  
• Next we assign a new instance of the PowerPoint application to the variable newPP, and add a new PowerPoint presentation.
• Now we can start to loop through each embedded chart in the active worksheet and copy each of them as a picture with the CopyPicture method.
• Before we can paste the chart in PowerPoint, we need to take the following steps:
• We add a new slide to the active presentation after the last slide (Slides.Count + 1). The second argument of the Add method determines the type of slide added. The number 12 corresponds to a blank slide.
• We select or go to the active window view of the new added slide with the GoToSlide method. Slids.Count is now the last slide (just added in the previous step).
• Finally, we paste the embedded chart as a picture on the active window view of the PowerPoint presentation.

This is how we copy/paste embedded charts from Excel to PowerPoint using VBA macros.

Other examples:

• How to Update Web Query On File Open with Excel VBA macros
  
• How to Insert Picture From Google Drive with Excel VBA macros
  
• How to AutoFill Table With SpecialCells with Excel VBA macros