fronzenite
New Member
- Joined
- Jun 3, 2018
- Messages
- 1
Hi all,
I'm pretty new here. I have been struggling for quite awhile with a present code which i would highly appreciate if i could get some help.
I have a workbook with multiple sheets on it, and would like the VBA code to loop through all the worksheets, copying table from two cell ranges and pasting them into powerpoint, while keeping source formatting (so that the two tables will be in table form which is editable), as well as positioning one table left, and the other to the right.
As of now, i have managed to come up with the following code, but the loop does not seem to work.
Thanks in advance!
I'm pretty new here. I have been struggling for quite awhile with a present code which i would highly appreciate if i could get some help.
I have a workbook with multiple sheets on it, and would like the VBA code to loop through all the worksheets, copying table from two cell ranges and pasting them into powerpoint, while keeping source formatting (so that the two tables will be in table form which is editable), as well as positioning one table left, and the other to the right.
As of now, i have managed to come up with the following code, but the loop does not seem to work.
Thanks in advance!
Code:
Sub WorkbooktoPowerPoint()
'Step 1: Declare your variables
Dim pp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim xlwksht As Worksheet
Dim MyRange As String
Dim MyRange1 As String 'Define another Range
Dim MyTitle 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: Set range for Both Tables here
MyRange = "B15:F40"
MyRange1 = "H15:L42"
'Step 4: Start the loop through each worksheet
For Each xlwksht In ActiveWorkbook.Worksheets
xlwksht.Select Application.Wait(Now + TimeValue("0:00:5"))
'Step 5: Copy First Table Range
xlwksht.Range(MyRange).Copy
'Step 6: Count slides and add new blank slide as next available slide number '(the number 12 represents the enumeration for a Blank Slide)
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
PPSlide.Select
'Step 7: Paste First Table Range into PPT
pp.CommandBars.ExecuteMso ("PasteSourceFormatting")
'Step 8: Copy Second Table and Paste Range into PPT
xlwksht.Range(MyRange1).Copy
pp.CommandBars.ExecuteMso ("PasteSourceFormatting")
Next xlwksht
'Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub