FOR EACH WS starts at the very last sheet. Why?

[VBAWannabee2]

Hi all!

My concern is what exactly the thread title is.
This is the code that I used to copy excel data then paste it on PPT but it starts the loop at the very last sheet.
Why is that? Appreciate the help in advance!

Sub CreateNewPres()

    Dim ppt As PowerPoint.Application
    Dim pres As PowerPoint.Presentation
    Dim sh As PowerPoint.ShapeRange
    Dim sl As PowerPoint.Slide
    Dim ws As Worksheet
    
    Set ppt = GetObject(Class:="Powerpoint.Application")
    Set pres = ppt.Presentations("TESTFILE - Copy.pptx")

For Each ws In ActiveWorkbook.Worksheets

If ws.Visible = True Then

    Set sl = pres.Slides.Add(3, ppLayoutBlank)

    ws.Range("A1:D8").Copy

    Set sh = sl.Shapes.PasteSpecial(ppPasteEnhancedMetafile)
    
    'sh.Top = 66
    'sh.Left = 152
    sh.LockAspectRatio = msoFalse
    sh.ScaleHeight 1.24, msoTrue, msoScaleFromMiddle
    sh.ScaleWidth 1.33, msoTrue, msoScaleFromMiddle
    
End If

Next ws

MsgBox "Process done!"

End Sub

 

[RoryA]

it starts the loop at the very last sheet

What are you basing that on?

 

[VBAWannabee2]

What are you basing that on?

Hi RoryA!

I think I got.

This line..

Set sl = pres.Slides.Add(3, ppLayoutBlank)

Since it starts to paste the data at the 3rd slide, the first sheet will be pasted to slide 3, then the 2nd sheet will be pasted again on slide 3.
The first sheet that was copied first will be now on slide 4 that's why I thought it starts at the end of the sheet. My bad.

But how can I alter that line though? so that it would paste the excel data respectively starting slide 3?
Currently, I have 14 sheets.

Thank RoryA!

 

[RoryA]

You can use a variable and increment it each time, or you could simply add it to the end each time using pres.slides.count + 1 instead of 3?

 

[VBAWannabee2]

You can use a variable and increment it each time, or you could simply add it to the end each time using pres.slides.count + 1 instead of 3?

Hi RoryA!

Can you run me thru the usage of a variable? If you don't mind.
In the PPT presentation that I have, the first 2 slide was like the title/intro thingy.
That's why the pasting of excel data starts at slide 3.

Tried this

pres.slides.count + 1

but all of the data that was pasted was at the first slide disregarding the first 2 slide (which is my title/intro).
If you know what I mean.

 

[Skyybot]

Set sl = pres.Slides.Add(pres.Slides.Count + 1, ppLayoutBlank)

 

[VBAWannabee2]

Hi Skyybot!!

I misunderstood RoryA's reply. (I'm sorry RoryA) I added this line and it WORKS however I forgot to mention that there's a 3 slides at the very end of my PPT. (yikes)
So it's like (1st & 2nd slide - for intro/title, 3rd-5th slide was another title/intro for different topic)

Set sl = pres.Slides.Add(pres.Slides.Count + 1, ppLayoutBlank)

 

[Skyybot]

So, am I to understand that you want to add a slide in between existing slides?

 

[VBAWannabee2]

So, am I to understand that you want to add a slide in between existing slides?

Yup. I really forgot to mention the slides that I'm having.

 

[Skyybot]

Try

Set sl = pres.Slides.Add(pres.Slides.Count - 3, ppLayoutBlank)