Refer to "value/range" to put chart in specific PPT slide (VBA)

[Djani]

Hi all,

I have a properly working VBA script that copy-pastes the chart into a slide, but I want it to refer to a specific cell so VBA 'knows' in what slide the chart has to be put. This is the code:

Sub ChartToPresentation()
         
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim shp As String
Dim newShape As PowerPoint.ShapeRange


' Make sure a chart is selected
If ActiveChart Is Nothing Then
    MsgBox "Please select a chart and try again.", vbExclamation, _
        "No Chart Selected"
Else
    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    ' Reference active slide
    Set PPSlide = PPPres.Slides(3)
               
    ' Copy chart as a picture
    ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
        Format:=xlPicture

    ' Paste chart
    Set newShape = PPSlide.Shapes.Paste

With newShape
    .IncrementLeft 400
    .IncrementTop 250
    .ScaleWidth 0.87, msoFalse, msoScaleFromTopLeft
    .ScaleHeight 0.87, msoFalse, msoScaleFromTopLeft
End With

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

End Sub

However, instead of putting a 'hard value' in it, I want it to use a "range" called "PPTSlide" (from Name Manager). Is it possible to change the script where I change:

    ' Reference active slide
    Set PPSlide = PPPres.Slides(3)

In something like this?

Set PPTSlide = ActiveSheet.Range("PPTSlide").Value

I tried to paste this piece of code into the new one, but it said "Object required".

Yours sincerely,

Djani

 

[Domenic]

Try...

Set PPSlide = PPPres.Slides(ActiveSheet.Range("PPTSlide").Value)

Hope this helps!