Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Pasting multiple ranges from excel into powerpoint slide as link [Closed]

Report
-
Hello,

I'm an absolute beginner in VBA. I'm using the code below to copy and paste multiple ranges into PowerPoint slides. Instead of copying and pasting the cell ranges as pictures, how can I change the code to paste the ranges as linked excel worksheet objects? (Manually, this is done by clicking paste special > paste link > Microsoft Excel Linked Object)

Sub WorkbooktoPowerPoint()

'Step 1: Declare your
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: Set the ranges for your data and
MyRange = "B4:J25"

'Step 4: Start the loop through each worksheet
For Each xlwksht In ActiveWorkbook.Worksheets
xlwksht.Select
Application.Wait (Now + TimeValue("0:00:1"))

'Step 5: Copy the range as picture
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 the picture and adjust its position
PPSlide.Shapes.Paste.Select
pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True


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

'Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub

Any guidance is much appreciated, thank you!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!