VBA Excel - Powerpoint export - Dynamically copy as Images or Tables

Hi everyone!

I've the following piece of code which exports excel sheets to powerpoint. In each sheet it takes the range from cells A1 and A2 and copies that range into powerpoint.
Now I want to add two functions, but I'am stuck to fix this, so I am hoping anyone can help me with this?

1 - In sheets where only a table is included, the code does exactly what it's supposed to do. However in some sheets I've included a picture or a chart and these are not properly pasted in excel. (only a blank picture is copied in the powerpoint slide). Now I want to make a code that uses my input from cell "C1" to determine whether this slide needs to be pasted as an image or as a normal paste. I've tried to fix this but my code continuously gets an error. Is there any way that I can adjust this code so that I will get it working? (It's going wrong at step 5 - "PasteType")

2 - The code now copies all worksheets, but I want it to start at sheet 7 and continue from there till the end. Thus skipping the first 6 worksheets. Does anyone have a clue how I can exclude these sheets in my VBA?



Sub PrintPPT()
'Step 1: Declare variables
Dim pp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim xlwksht As Worksheet
Dim MyRange As String

'Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True

'Step 3: Start the loop through each worksheet
For Each xlwksht In ActiveWorkbook.Worksheets
MyRange = xlwksht.Range("A1").Value & ":" & xlwksht.Range("A2").Value
xlwksht.Range(MyRange).Copy

'Step 4: Count slides and add new blank slide as next available slide number
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
PPSlide.Select

'Step 5: Paste the picture and adjust its position
PPPres.ApplyTemplate ("C:/My location/template.potx")

PasteType = xlwksht.Range("C1").Value
' Pastetype is "PasteSpecial DataType:=2" for images
' Pastetype is "Paste.Select" for normal

PPSlide.Shapes.PasteType

pp.ActiveWindow.Selection.ShapeRange.Top = 85
pp.ActiveWindow.Selection.ShapeRange.Left = 7.2

'Step 6: Add the title to the slide then move to next worksheet
Next xlwksht

'Step 7: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing

End Sub

Thanks for your help Sean! That worked as perfectly as I had hoped!
Do you maybe have a solution for my second question as well regarding the skipping of the first 6 excel sheets?

Hi Sean, thanks again for your help, however this time I can't get either of them working. I've added your code in my step 3

'Step 3:
For Each xlwksht In ActiveWorkbook.Worksheets
If xlwksht.Name = "Sheet1" Then
Else
MyRange = xlwksht.Range("A1").Value & ":" & xlwksht.Range("A2").Value
xlwksht.Range(MyRange).Copy
End If
Next

But if I try this, the program gives an error in step 7 "Next without For".
If I leave the "next" out it gives an error on step 5..

Do you maybe know what I am doing wrong?