Search code examples
arraysexcelvbapowerpoint

VBA - Turning Array to a For loop

I was trying to automate the process of "pasting excel ranges to PPT". For example, paste A1:K39 in excel sheet7 to PPT slide2; paste A1:K39 in sheet8 to slide3; paste A1:K39 in sheet9 to slide4, etc.

It worked if I type it the array one by one as followings:

Dim MySlideArray As Variant
Dim MyRangeArray As Variant
Dim x As Long

'List of PPT Slides to Paste to
 MySlideArray = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40)


'List of Excel Ranges to Copy from
 MyRangeArray = Array( _
      Sheet7.Range("A1:K39"), Sheet8.Range("A1:K39"), Sheet9.Range("A1:K39"), Sheet10.Range("A1:K39"), Sheet11.Range("A1:K39"), _
      Sheet12.Range("A1:K39"), Sheet13.Range("A1:K39"), Sheet14.Range("A1:K39"), Sheet15.Range("A1:K39"), Sheet16.Range("A1:K39"), _
      Sheet17.Range("A1:K39"), Sheet18.Range("A1:K39"), Sheet19.Range("A1:K39"), Sheet20.Range("A1:K39"), Sheet21.Range("A1:K39"), _
      Sheet22.Range("A1:K39"), Sheet23.Range("A1:K39"), Sheet24.Range("A1:K39"), Sheet25.Range("A1:K39"), Sheet26.Range("A1:K39"), _
      Sheet27.Range("A1:K39"), Sheet28.Range("A1:K39"), Sheet29.Range("A1:K39"), Sheet30.Range("A1:K39"), Sheet31.Range("A1:K39"), _
      Sheet32.Range("A1:K39"), Sheet33.Range("A1:K39"), Sheet34.Range("A1:K39"), Sheet35.Range("A1:K39"), Sheet36.Range("A1:K39"), _
      Sheet37.Range("A1:K39"), Sheet38.Range("A1:K39"), Sheet39.Range("A1:K39"), Sheet40.Range("A1:K39"), Sheet41.Range("A1:K39"), _
      Sheet42.Range("A1:K39"), Sheet43.Range("A1:K39"), Sheet44.Range("A1:K39"), Sheet45.Range("A1:K39"))

'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 = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
      On Error GoTo 0
    
  Next x

I try to write a for loop for slide array and range array as followings - while it didn't work as I wanted. It paste A1:K39 in sheet45 to PPT slide40 only. There's only one page.

'List of PPT Slides to Paste to
  For i = 2 To 40
    MySlideArray = Array(i)
  Next i

'List of Excel Ranges to Copy from
  For J = 7 To 45
    MyRangeArray = Array(Sheets(J).Range("A1:K39"))
  Next J

Could anyone help advise how to correct the code? This is very beginning of my VBA journey so I would really appreciate your help and suggestion. Thanks a million!

Solution

  • Here is a simple example:

    Sub PasteExcelRangeToPowerPoint()    
    Dim powerpoint As Object, presentation As Object, slide As Object, i as Integer
    
    Set powerpoint = CreateObject(class:="PowerPoint.Application")
    Set presentation = powerpoint.Presentations.Add
    
    For i = 2 To 40
        Set slide = presentation.Slides.Add(1, 11)
        
        Worksheets(i + 5).Range("A1:K39").Copy
        slide.Shapes.PasteSpecial DataType:=1
    Next i
    
    powerpoint.Visible = True
    powerpoint.Activate      
End Sub

    Notes

    1. The loop creates 39 slides with pasted excel data. Note that as your sheet references are sequential (7, 8, 9...) then you can capture that using i + 5. Your range reference does not change.
    2. DataType:=1 specifies the paste format. To see all enumerations refer to this link https://learn.microsoft.com/en-us/office/vba/api/powerpoint.pppastedatatype.