VBA - Turning Array to a For loop

Question

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!

How does it work in any way when you won't change your slide? `Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange` this is the current slide I presume and you never change it. — Damian, Sep 23 '20 at 06:57
Are you pasting to an existing powerpoint presentation or are you creating a new one? — Alex P, Sep 23 '20 at 07:01

score 1 · Accepted Answer · answered Sep 23 '20 at 07:18

1

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

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.
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.

answered Sep 23 '20 at 07:18

Alex P

12,249
5
51
70

Hi Alex, thanks a lot for your help!! This works but I have two follow-up questions. (1) I actually want to paste them to existing slides with title I typed down beforehand. I change it to ```presentation = powerpoint.ActivePresentation```, but still not figuring out how to paste to existing slide. – Enya Sep 23 '20 at 09:17
(2) The code is pasting excel to PPT in a reverse order. for example sheet7 is pasted as slide 40, sheet8 is pasted as slide 39. While I need them to be pasted in the opposite way, Will you know how to fix this? Thanks a million for sharing with me. – Enya Sep 23 '20 at 09:20
`For i = 40 to 2 Step -1` should solve the ordering issue. Every new slide created is added at the end of the slide collection. For your other issue, you need to (a) open your existing .ppt, and (2) update the loop to get a reference to the appropriate slides (e.g. `slide(i).Shapes.PasteSpecial DataType:=1`) – Alex P Sep 23 '20 at 09:39
Thank you Alex for helping me out. The ordering issue is solved now. I'm studying on how to update the loop now. Appreciate! – Enya Sep 24 '20 at 03:41

score -1 · Answer 2 · answered Sep 23 '20 at 07:01

-1

That's why error in declare-AND-set Array

'Array init
ReDim MySlideArray(0 to 38)
'List of PPT Slides to Paste to
  For i = 2 To 40
    MySlideArray(i-2) = i
  Next i

'Array init
ReDim MyRangeArray (0 to 38)
'List of Excel Ranges to Copy from
  For J = 7 To 45
    MyRangeArray(j-7) = ThisWorkbook.workSheets("Sheet" & j).Range("A1:K39"))
  Next J

answered Sep 23 '20 at 07:01

Sacru2red

127
1
4

Hi @Sacru2red, first of all thanks for your kind reply. I try your codes and the upper section works! However the lower section (range array) keeps showing error 9 - Subscript out of range. Would you maybe know how to fix this problem? Thanks again! – Enya Sep 23 '20 at 07:38
@Enya ah.. `ThisWorkbook.workSheets("Sheet" & j).Range("A1:K39"))` is wrong. Sheet7... Sheet45 is code Name not sheet Name.. so can't use `ThisWorkbook.workSheets("Sheet" & j)` .. – Sacru2red Sep 23 '20 at 07:43
can u change sheet Name Like sheet Code Name ? refer https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename – Sacru2red Sep 23 '20 at 07:46
It didn't work.. After referring to sheet by CodeName, there's still error 9. No matter what, I appreciate your help Sacru2red! Thanks. – Enya Sep 24 '20 at 03:39
@Enya can not referece `worksheets(index)` expression with worksheet code name – Sacru2red Sep 24 '20 at 04:48

VBA - Turning Array to a For loop

2 Answers2