Can I paste into powerpoint to a specific slide based on a cell value?

[greekmac7]

Hi everyone,

Currently I have a macro which allows me to paste from multiple sheets in an excel workbook to multiple pages in a powerpoint project:

Sub PasteMultipleSlides()

'PURPOSE: Copy Excel Ranges and Paste them into the Active PowerPoint presentation slides
'SOURCE: www.TheSpreadsheetGuru.com

Dim myPresentation As Object
Dim mySlide As Object
Dim PowerPointApp As Object
Dim shp As Object
Dim MySlideArray As Variant
Dim MyRangeArray As Variant
Dim x As Long

'Create an Instance of PowerPoint
  On Error Resume Next
    
    'Is PowerPoint already opened?
      Set PowerPointApp = GetObject(class:="PowerPoint.Application")
    
    'Clear the error between errors
      Err.Clear

    'If PowerPoint is not already open then Exit
      If PowerPointApp Is Nothing Then
        MsgBox "PowerPoint Presentation is not open, aborting."
        Exit Sub
      End If
    
    'Handle if the PowerPoint Application is not found
      If Err.Number = 429 Then
        MsgBox "PowerPoint could not be found, aborting."
        Exit Sub
      End If

  On Error GoTo 0
  
'Make PowerPoint Visible and Active
  PowerPointApp.ActiveWindow.Panes(2).Activate
    
'Create a New Presentation
  Set myPresentation = PowerPointApp.ActivePresentation

'List of PPT Slides to Paste to
  MySlideArray = Array(1, 2, 3, 4, 5, 6)

'List of Excel Ranges to Copy from
    MyRangeArray = Array(Sheet1.Range("A1:K34"), Sheet4.Range("A1:K34"), _
      Sheet3.Range("A1:K34"), Sheet2.Range("A1:K34"), Sheet5.Range("A1:K34"), Sheet6.Range("A1:K34"))

'Loop through Array data
  For x = LBound(MySlideArray) To UBound(MySlideArray)
    'Copy Excel Range
        MyRangeArray(x).Copy
    
    'Paste to PowerPoint and position
      On Error Resume Next
        Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
        Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
      On Error GoTo 0
    
    'Center Object
      With myPresentation.PageSetup
        shp.Left = (.SlideWidth \ 2) - (shp.Width \ 2)
        shp.Top = (.SlideHeight \ 2) - (shp.Height \ 2)
      End With
      
  Next x

'Transfer Complete
  Application.CutCopyMode = False
  ThisWorkbook.Activate
  MsgBox "Complete!"

End Sub

This macro works really well for me, however what I would love to do is control the slide number array based on cell values in the workbook.

The reason for this is that the page numbers might be different from one issue to the next.
Rather than having my slide array as (1,2,3,4,5,6)
I'd love to be able to have something like my slide array
(1,2,3,4,5,6)

I'd love to be able to have (sheet1.range("A1").value,sheet2.range("A1").value.......)

Is this possible?


Many thanks in advance.

Ed.