Excel to Powerpoint with Source Format

[The_Rock]

Hi Guys
I have a code which copies from Excel to Powerpoint which works great.
I am not trying to tweak it with the following bit of code to preseve the source format.

   PPSlide.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")

What I then need to do is select the pasted chart and move it to the correct position. Its pasting as Chart 6 and so I am doing the following:

    PPApp.ActiveWindow.Selection.SlideRange.Shapes("Chart 6").Select

Now this is where it throws up a debug error "Item Chart 6 not found in the shapes collection".
If I click on Debug and continue to run the macro, it will run.
I need something in there that refreshes the powerpoint chart so it knows Chart 6 is there.

Hope you can help

This is the extract of the code:

    IP.Activate
    ActiveSheet.ChartObjects("Chart 3").Activate 'Select Chart
    PPApp.ActiveWindow.ViewType = 1 
    ActiveChart.ChartArea.Copy
    ' Paste chart
    PPSlide.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")
    PPApp.ActiveWindow.Selection.SlideRange.Shapes("Chart 6").Select
    ' Position pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Left = 0.75 * 28.3464567
    PPApp.ActiveWindow.Selection.ShapeRange.Top = 2 * 28.3464567

 

[Domenic]

With the following code, you'll notice that there's no need to select the chart before copying it, and that it also includes a 3 second delay to allow the chart time to be copied and pasted. You may find that a 1 second delay will suffice.

[FONT=Courier New]    ActiveSheet.ChartObjects("Chart 3").Chart.ChartArea.Copy
    
    PPApp.ActiveWindow.ViewType = 1
    PPSlide.Application.CommandBars.ExecuteMso ("PasteSourceFormatting")
    
    EndTime = Timer + 3 [COLOR=green]'seconds delay[/COLOR]
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Timer < EndTime
        DoEvents
    [COLOR=darkblue]Loop[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] PPApp.ActiveWindow.Selection.ShapeRange(1)
        .Left = 0.75 * 28.3464567
        .Top = 2 * 28.3464567
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [/FONT]

Also, you should delcare EndTime, along with your other variables (assuming that you've done so)...

[FONT=Courier New]    [COLOR=darkblue]Dim[/COLOR] EndTime [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR][/FONT]

Hope this helps!

 

[The_Rock]

Awesome, thank you very much Domenic - works perfectly