I have indeed left out this piece of code:
Set ppSlide = ppPres.Slides(5)
However, this is the 'hard value'. It does work whenever I put this piece of code back into the script, but it doesn't change/manipulate the behavior --> it is set. I want the script to refer to cell B6 since this cell VLOOKUPs the combination of country and model --> returns desired PPT slide. I have left this out, because I can't simply do the following right?
Set ppSlide = ppPres.Slides(Worksheets("VIVA GRAPH").B6)
Correct me if I'm wrong, but in this case the slide is defined as a cell, which can't ever be the case. I have tried some other things, but it doesn't lead me to a solution. This is what I have:
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
Dim rng As Range
Dim cell As Range
Dim i As Integer
i = Worksheets("VIVA GRAPH").Range("PPTSlide")
' 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(i)
' 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
It gives the error on Set PPSlide = PPPres.Slides(i) --> Integer out of range. 0 is not in the valid range of 1 to 4.
P.S: It is working partially, all numbers above 5 will give the abovementioned error "... valid range of 1 to 5"