Microsoft Office Tutorials and References
In Depth Information
Copying a Worksheet Range to a PowerPoint Slide
‘Create a new Presentation and add a slide.
Set ppPres = ppApp.Presentations.Add
With ppPres.Slides
‘11 is the numeric Constant for ppLayoutTitleOnly.
‘The Constant is used with late binding.
Set ppSlide = .Add(.Count + 1, 11)
End With
‘Save your new file.
ppPres.SaveAs Filename:=ThisWorkbook.Path & “\CreateTest.pptx”
‘Release system memory reserved for the Object variables.
Set ppApp = Nothing
Set ppPres = Nothing
Set ppSlide = Nothing
End Sub
copying A WorksHEET rAngE To A poWErpoinT slidE
Now that you have just created a PowerPoint presentation file, while it’s still open, suppose you
want to copy a worksheet range into that presentation’s first slide. The following macro uses an
InputBox for the user to select a range to be copied.
Please note that this macro relies on the PowerPoint presentation file to be
open. The code will not copy an Excel worksheet range to a closed PowerPoint
presentation.
One consideration to monitor is the selected size of a range, as you can see in the code. My
column and row limitations are just for example purposes. Whatever limit, if any, that you decide,
the objective should be to place a clear, concise image on the slide.
Sub CopyRange()
‘Declare a Range type variable
Dim rng As Range
‘Use an Application InputBox to have the user select the desired range.
‘Exit the macro if the user cancels.
On Error Resume Next
Set rng = Application.InputBox(“Select a range to be copied:”, Type:=8)
If Err.Number<> 0 Then
Err.Clear
MsgBox “You did not enter a range.”, vbInformation, “Cancelled”
Exit Sub
End If
‘Monitor the size of the range so an unreasonably large range is not attempted.
If rng.Columns.Count> 6 Or rng.Rows.Count> 20 Then
MsgBox “You selected a range that is too large.” & vbCrLf & _
 
Search JabSto ::




Custom Search