Hello Everyone,

I wrote a macro that pastes 2 different tables from excel to the same slide in the Power Point presentation (one table above the other one). To keep source formatting I'm using PPApp.CommandBars.ExecuteMso ("PasteSourceFormatting") method. The macro uses already designed Power Point template with Titles and slide numbers in it. The problem is that the macro sometimes works properly but other times it pastes the table but then it moves the Title or page number to the place where the table should be moved as requested in the code (see the code below for details). It seems that Shapes(.Shapes.Count) is not always the same as the table that has already been pasted. Is there any way to define some kind of shape for PPApp.CommandBars.ExecuteMso ("PasteSourceFormatting") method - asign shape to that commend (e.g. when I'm coping picture and pasting it to power point I can do that in the following way: Set objNew = PPSlide.Shapes.Paste and then I can move objNew to correct place on the slide but not sure how to do the same when using this CommandBar methodology). Please advise in case you know how to fix that. Any help is appreciated!

Thank you,Joanna.


Macro that I use:

Set PPApp = CreateObject("Powerpoint.Application")
PPApp.Visible = True
Set PPPres = PPApp.Presentations.Open(Filename:="path\PPT_template.pptx")

iSlide = 1

Windows("excel_file.xlsx").Activate
Sheets("excel_sheet").Select

Range("B1:M9").Copy
Call PastewithSourceFormatting(iSlide, 725, 170, 140, 32)

Range("B12:M18").Copy
Call PastewithSourceFormatting(iSlide, 725, 200, 330, 32)

And the supporting Sub:

Sub PastewithSourceFormatting(iSlide As Integer, W As Integer, H As Integer, T As Integer, l As Integer)

Set PPSlide = PPPres.Slides(iSlide)
PPSlide.Select

With PPSlide
PPApp.CommandBars.ExecuteMso ("PasteSourceFormatting")
Dim iter As Integer
For iter = 1 To 150
DoEvents
Next iter
PPApp.CommandBars.ReleaseFocus
With .Shapes(.Shapes.Count) ' sizes the table on the slide
.LockAspectRatio = msoFalse
.ScaleWidth 1, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
.Width = W
.Height = H
.Top = T
.Left = l
End With
End With
Application.CutCopyMode = False
Exit Sub
End Sub