[Excel VBA] How to Paste an Excel Table to PowerPoint and Adjust Size and Position

Pasting tables created in Excel into PowerPoint slides is a frequent task in report creation. Using VBA, you can not only automate this copy-and-paste process but also precisely specify the size and position of the pasted table on the slide.

In this article, I will explain practical VBA code to copy an Excel cell range and paste it as a table of a specified size at a specific position on a PowerPoint slide.

[Important] Setting References (Recommended)

To use PowerPoint functions smoothly in VBA, I strongly recommend setting up References beforehand.

1. Open the VBE (Alt+F11) and click Tools > References in the menu.
2. Find Microsoft PowerPoint XX.0 Object Library in the list, check the box, and click OK.

This makes PowerPoint-specific commands and constants available, making the code much easier to handle.

Completed VBA Code

Below is the VBA code that copies an Excel table, pastes it into a PowerPoint slide, and adjusts its position and size.

' Reference: Microsoft PowerPoint XX.0 Object Library
Sub PasteTableToPowerPoint()

    ' Declare variables
    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim targetSlide As PowerPoint.Slide
    Dim pastedTableShape As PowerPoint.Shape
    Dim sourceRange As Range

    ' --- 1. Launch PowerPoint and Prepare a Slide ---
    Set ppApp = New PowerPoint.Application
    ppApp.Visible = True ' Make the application visible

    Set ppPres = ppApp.Presentations.Add
    ' Add one blank slide
    Set targetSlide = ppPres.Slides.Add(1, ppLayoutBlank)

    ' --- 2. Copy the Excel Table Range ---
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:E8")
    sourceRange.Copy

    ' --- 3. Paste to PowerPoint Slide and Get the Object ---
    ' The Paste method returns the pasted Shape object
    Set pastedTableShape = targetSlide.Shapes.Paste

    ' --- 4. Adjust Position and Size of the Pasted Table ---
    With pastedTableShape
        .Left = 130 ' Position from left
        .Top = 100  ' Position from top
        .Width = 700 ' Width
        .Height = 250 ' Height
    End With

    ' --- 5. Save and Quit ---
    ppPres.SaveAs ThisWorkbook.Path & "\PastedTablePresentation.pptx"
    ppApp.Quit

    ' --- 6. Release Objects ---
    Set pastedTableShape = Nothing
    Set targetSlide = Nothing
    Set ppPres = Nothing
    Set ppApp = Nothing

    MsgBox "The table has been pasted to PowerPoint."

End Sub

Explanation of Key Points

1. Copying the Excel Range

Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:E8")
sourceRange.Copy

First, store the cell range on Excel that you want to paste into PowerPoint into an object variable, and copy it to the clipboard using the .Copy method.

2. Pasting to PowerPoint Slide (.Shapes.Paste)

Set pastedTableShape = targetSlide.Shapes.Paste

This is the core part of this code. targetSlide.Shapes.Paste pastes the content from the clipboard onto the slide. Most importantly, this method returns the newly created Shape object as a return value.

By Setting this return value to the pastedTableShape variable, you can directly manipulate the table object you just pasted using VBA.

3. Adjusting Position and Size of the Pasted Table

With pastedTableShape
    .Left = 130
    .Top = 100
    .Width = 700
    .Height = 250
End With

You can adjust the display of the table by manipulating the properties of the acquired pastedTableShape object.

• .Left: Position from the left edge of the slide.
• .Top: Position from the top edge of the slide.
• .Width: Width of the table.
• .Height: Height of the table.

The unit for these values is Points (1 inch = 72 points). By adjusting these values, you can place the table at any position and size on the slide.

4. Save and Quit

ppPres.SaveAs ...
ppApp.Quit

Finally, save the created presentation with .SaveAs and close the PowerPoint application with .Quit. This prevents memory leaks and completes the process safely.

Summary

The key to pasting an Excel table into PowerPoint and adjusting its layout is to receive the return value of the .Shapes.Paste method.

1. Execute .Copy on the Excel side.
2. Execute .Shapes.Paste on the PowerPoint side and store its return value in a Shape variable.
3. Rewrite properties like .Left and .Width of that Shape variable to adjust the layout.

By following this procedure, you can fully automate not only pasting data but also generating slides with a polished design.