Hello Comrades,
I am new to Excel VBA so please excuse any misuse of excel terminologies. I used some code I found on a Youtube tutorial video to export from excel to PowerPoint using VBA. The code worked to an extent until I tried to modify it - now I'm stuck.
I want to export a range of cells to PowerPoint but the cell values change based on a scrollbar I inserted. This is what it looks like:
I want to export the puzzle, which is Range("M1:U11"), but I want each puzzle from 1 to 300 to appear on its own separate slide on the Powerpoint Presentation. So When A6 = 1, puzzle no 1 is exported, when A = 2, puzzle no 2 is exported, and so on and so forth.
I tried adding a For and if loop (I don't fully know how thoses work tbh) but it copied and pasted cell "A6" a 155 times! Long story short, I'm stuck and I need your help. Here's the code:
Thanks in advance!
I am new to Excel VBA so please excuse any misuse of excel terminologies. I used some code I found on a Youtube tutorial video to export from excel to PowerPoint using VBA. The code worked to an extent until I tried to modify it - now I'm stuck.
I want to export a range of cells to PowerPoint but the cell values change based on a scrollbar I inserted. This is what it looks like:
I want to export the puzzle, which is Range("M1:U11"), but I want each puzzle from 1 to 300 to appear on its own separate slide on the Powerpoint Presentation. So When A6 = 1, puzzle no 1 is exported, when A = 2, puzzle no 2 is exported, and so on and so forth.
I tried adding a For and if loop (I don't fully know how thoses work tbh) but it copied and pasted cell "A6" a 155 times! Long story short, I'm stuck and I need your help. Here's the code:
VBA Code:
Private Sub CommandButton1_Click()
'Declare our Variables
Dim r As Range
Dim powerpointapp As Object
Dim mypresentation As Object
Dim myslide As Object
Dim myshape As Object
'assigning range into variable
Set r = ThisWorkbook.Worksheets("Sheet1").Range("M1:U11")
'If we have already opened powerpoint
Set powerpointapp = GetObject(class:="PowerPoint.Application")
'If powerpoint is not opened
If powerpointapp Is Nothing Then Set powerpointapp = CreateObject(class:="PowerPoint.Application")
'To create a new presentation
Set mypresentation = powerpointapp.Presentations.Add
'Loop through all the values in "A6"
'If Range("A6") = loop 1 To 100 Then Range("M1:U11").Select
'Copy the Selection
'Paste each iteration on a new slide on Powerpoint
Set myslide = mypresentation.slides.Add(1, 11)
r.Copy
'to paste range
myslide.Shapes.PasteSpecial DataType:=2
Set myshape = myslide.Shapes(myslide.Shapes.Count)
myshape.Left = 250
myshape.Top = 150
powerpointapp.Visible = True
powerpointapp.Activate
'to clear the cutcopymode from clipboard
Application.CutCopyMode = False
'Keep going if thee is an error
On Error Resume Next
End Sub
Thanks in advance!