I am new to this forum, and new to VBA. I am working with a very large Excel table (200+ rows) where I have added a code in to identify page breaks based on when a value changes in one of the columns. I've figured out how to paste my range of data into a PowerPoint slide, but I need it to paste onto separate slides according to where/when the page break is set.
Here is my page break code:
J = ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row
For I = J To 2 Step -1
If Range("I" & I).Value <> Range("I" & I - 1).Value Then
ActiveSheet.HPageBreaks.Add Before:=Range("I" & I)
End If
Next I
And here is what I have so far to get range into PowerPoint, but this pastes everything on one slide:
Dim rng As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object
'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.Range("A1:J5")
'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 open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
'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
'Optimize Code
Application.ScreenUpdating = False
'Create a New Presentation
Set myPresentation = PowerPointApp.Presentations.Add
'Add a slide to the Presentation
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
'Copy Excel Range
rng.Copy
'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange
'Center Object
With myPresentation.PageSetup
shp.Left = (.SlideWidth \ 2) - (shp.Width \ 2)
shp.Top = (.SlideHeight \ 2) - (shp.Height \ 2)
'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate
'Clear The Clipboard
Application.CutCopyMode = False
End With
Set rng
andset myslide
andrng.copy
are together, so you can loop the range-setting and copying. Then: I don't know anything about page breaks, but it seems clear that your two options here are: 1. Keep your two-sub structure; research page breaks and how you can get the code to detect one, and loop the range-set-and-copy every time you encounter a page break; 2. Dump the page break thing and incorporate the for each row loop in the first sub into the loop in the main sub using the range().value <> range(,-1).value setup.