Re-activating XL VBA error after pasting into powerPoint

[Johnny C]

I have a utility that copies charts and named ranges into PowerPoint slides.

It worked fine until I added some code to (in bold below) resize the copied Shape and move it on the slide. Now it crashes with a Runtime error: 9 Subscript out of range (on the red line below)

The DoEvents loops just allow PP to do it's stuff before VBA continues. Extraneous stuff has been removed.

Set xlApp = Excel.Application
Set ppApp = GetObject(, "PowerPoint.Application")
Set ppSelected = ppApp.Presentations.Item(intPPCount)
Set activeSlide = ppSelected.Slides(intDocNum)
…
activeSlide.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=msoTrue
For i = 1 To 5000; DoEvents; Next i
[B]ppApp.ActiveWindow.Selection.ShapeRange.Height = sngHeight
ppApp.ActiveWindow.Selection.ShapeRange.Width = sngWidth
ppApp.ActiveWindow.Selection.ShapeRange.Left = sngLeft
ppApp.ActiveWindow.Selection.ShapeRange.Top = sngTop
[/B]activeSlide.Shapes(activeSlide.Shapes.Count).LinkFormat.BreakLink
For i = 1 To 5000;     DoEvents; Next i
activeSlide.Shapes(activeSlide.Shapes.Count).Fill.Solid
activeSlide.Shapes(activeSlide.Shapes.Count).Fill.ForeColor.RGB = RGB(255, 255, 255)
[B][COLOR=#ff0000]Sheets(strSheetName).Activate[/COLOR][/B]

strSheet has a valid sheet name.
I tried replacing the offending line with

xlApp.ActiveWorkbook.Sheets(strSheetName).Activate

but the same happens. In the locals window I can see xlApp, within there I can see Activeworkbook, within that I can see Sheets and the sheet with the name in strSheet exists.

What am I doing wrong?

 

[RoryA]

Pastespecial returns a shaperange, so perhaps use that directly rather than ppApp.ActiveWindow.Selection.ShapeRange?

 

[Johnny C]

Sorry to sound dumb, how do I do that?

There are 3 types of paste, depending on what the user selected.

activeSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile, Link:=msoFalse
ppApp.CommandBars.ExecuteMso "PasteSourceFormatting"
activeSlide.Shapes.PasteSpecial DataType:=ppPasteDefault, Link:=msoTrue
and then (subject to an IF)
ppApp.ActiveWindow.Selection.ShapeRange.ZOrder msoSendToBack

They were all working fine until I added the shape movements.

Editted to add: the executeMso paste is used because otherwise it doesn't paste charts correctly, IIRC the custom colours go out of whack.

 

[RoryA]

Declare a ShapeRange variable and use Set to assign the result of the PasteSpecial to it.

 

[Johnny C]

Cheers, I'll have a go.

 

[Johnny C]

I couldn't get that to work, however I fixed it by selecting the slide before I pasted into it, so the user can see the shape being pasted in and resized/located as it's done. It didn't need to pass control back to the original sheet. I must have deleted the slide activation statement for some obscure reason (probably thought I was deleting something else). it's been one of those days.