Referring to pasted shapes / pictures

[MikeBlake]

Hi there,
This is my first post on this site. Had some trouble with this issue for a while so any help would be greatly appreciated.

The problem is that I am using PasteSpecial function in VBA Excel to paste a shape (e.g. picture 27) from 1 sheet to another in the same workbook. I then need to clear all data in the 2nd sheet including deleting the pasted shape. Problem is that when the shape / picture gets pasted into the destination sheet it's name changes e.g. from picture 27 to picture picture 14 for example. Therefore I can't refer to it again so I can delete it automatically using another command button.



Set NokiaE66 = Sheets("Voda GP").Shapes("Group 38")
Dim AH As String
AH = Application.InputBox(prompt:="Enter model of handset to display:", Type:=2)
If InStr(AH, "E66") Then

NokiaE66.Copy
Sheets("VodaQuote").Range("B70").PasteSpecial
End If



How can I find out what the newley pasted shape object is automatically called inside the destination sheet?

Many thanks,
Mike

[lucas]

You could delete all of the shapes on sheet 2:
Option Explicit
Sub delShapes()
Dim Shp As Shape
For Each Shp In Sheets("Sheet2").Shapes
Shp.Delete
Next
End Sub

[Bob Phillips]

I would beware of deleting all shapes, unintended consequences can occur.

[Emily]

How can I find out what the newley pasted shape object is automatically called inside the destination sheet?


Selection.ShapeRange.Name

[Bob Phillips]

Selection.ShapeRange.Name

You will need to select that sheet as well.

[MikeBlake]

Many thanks for your help on this one. I'll go with deleting all the shapes on the destination sheet. The good thing is that the loop specifies the sheet name without having to do dodgy stuff like: Selection.Delete.

Cheers,

Mike

[Bob Phillips]

I hope you don't have any filters or data validation on those sheets.

[lucas]

Very good point Bob. I was not aware of that.

[Bob Phillips]

It's a beast to get them back, but it can be avoided with careful code.

[jolivanes]

I don't know how good your memory is Bob but does this look familiar to you?
Is it useable in this case?

Quote:

A more robust mechanism isVBA:



Sub RemoveShapes()
' Written by : Bob Phillips
' Inspired by: Debra Dalgleish & Dave Peterson
' Improved by: Dave Peterson (cater for forms combobox)
' Synopsis: Checks each shape to be form control, and if it
' is a dropdown, it aims to retain it.
' One problem is that the forms combobox which is
' also a form control, and is a dropdown, so it
' does not get deleted.
' Catered for by testing top left of shape, as
' Autofilter and Data Validation dropdowns do not
' seem to have a topleftcell address.
'---------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

sTopLeft = ""
On Error Resume Next
sTopLeft = shp.TopLeftCell.Address
On Error Goto 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If sTopLeft = "" Then
fOK = False 'keep it
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub

[Bob Phillips]

Indeed it is, it is the best way to delete all (floating) shapes from a worksheet.