VBA to copy paste all charts and tables from Excel file for all sheets to PowerPoint file

Question

I am new to VBA and presently copy pasting data from Excel to power point slides manually. Each PowerPoint slide has charts, text boxes and tables. So I want to copy data in the excel sheet and paste to PowerPoint without losing the original formatting and ability to change data in text boxes and tables. I found below macro which copy paste Excel sheet as a picture. But my problem is how can I paste the copied data in original format (i.e. Table, text box, chart, etc…). Any help is really appreciated.

Sub WorkbooktoPowerPoint()

    'Step 1:  Declare your variables
    Dim pp As Object
    Dim PPPres As Object
    Dim PPSlide As Object
    Dim xlwksht As Worksheet
    Dim MyRange As String
    Dim MyTitle As String

    'Step 2:  Open PowerPoint, add a new presentation and make visible

    Set pp = CreateObject("PowerPoint.Application")
    Set PPPres = pp.Presentations.Add
    pp.Visible = True

    'Step 3:  Set the ranges for your data and title

    MyRange = "A1:H40"  '<<<Change this range

    'Step 4:  Start the loop through each worksheet

    For Each xlwksht In ActiveWorkbook.Worksheets
        xlwksht.Select
        Application.Wait (Now + TimeValue("0:00:1"))

    'Step 5:  Copy the range as picture

        xlwksht.Range(MyRange).CopyPicture _
            Appearance:=xlScreen, Format:=xlPicture

    'Step 6:  Count slides and add new blank slide as next available slide number
    '(the number 16 represents the enumeration for a Blank Slide)

        SlideCount = PPPres.Slides.Count
        Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 16)
        PPSlide.Select

    'Step 7:  Paste the picture and adjust its position

        PPSlide.Shapes.Paste.Select
        pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        pp.ActiveWindow.Selection.ShapeRange.Top = 1
        pp.ActiveWindow.Selection.ShapeRange.Left = 1
        pp.ActiveWindow.Selection.ShapeRange.Width = 500

    'Step 8:  Add the title to the slide then move to next worksheet

    Next xlwksht

    'Step 9:  Memory Cleanup

    pp.Activate
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set pp = Nothing
End Sub

Answer 1

Try to use the copychart method intead of copypicture. It will retain the format and datasoyrce of the chart

Answer 2

First, thank you very much for making your code very easy to read and follow that makes this process so much easier!

For the most part, your code is spot on, we just have to change one section of it. The part we need to change is the paste section. We are going to change it to the PasteSpecial method because with this method we get a few more options on how we can paste it into PowerPoint.

Here is how the code will look after we change it from the Paste method to the PasteSpecial method:

'Paste the picture and adjust its position
PPSlide.Shapes.PasteSpecial DataType:=3  'This is ppPasteMetafilePicture

Keep in mind I passed through a new parameter which specifies the paste data type. I chose the ppPasteMetaFilePicture because you asked to keep the formatting. However, because we are using late binding in this code we have to use the enumeration which in this case is 3.

Now, unfortunately, pasting objects in PowerPoint or any office application can be very volatile sometimes, so there might be extra steps you need to take in order to make sure the code behaves as you expected. There are also several different ways you can paste a picture in PowerPoint and each has their own unique features.

I actually made a YouTube video where we go over how to paste objects from Excel to PowerPoint because it is such a common request. If you'd like to see the video you can just follow the link below.

https://youtu.be/cpwHL26Nxhc

Full disclosure this is my personal YouTube account.