Hi all,

I have an issue where data is copied and pasted from an email into a sheet
where the data must be pasted in as Paste All -- the issue is that there are
objects of some sort that get pasted in also. When the code executes, I want
to have the objects (which get pasted in with the names "AutoShape 2544",
"AutoShape 2545", etc.) deleted.

The problem is that I have two command buttons from the Control Toolbox on
the sheet so if I use the code below, the command buttons also get deleted.
Any idea on how I can select all shapes but the two command buttons (Names
"CommandButton1" and "CommandButton2")?

Sheets("Sheet1").Activate
Shapes.SelectAll
Selection.Delete

Thanks,

--
Robert

For Each shp In ActiveSheet.Shapes
If shp.ID = 2544 Then
shp.Delete
End If

Next shp


"robs3131" wrote:

Hi all,

I have an issue where data is copied and pasted from an email into a sheet
where the data must be pasted in as Paste All -- the issue is that there are
objects of some sort that get pasted in also. When the code executes, I want
to have the objects (which get pasted in with the names "AutoShape 2544",
"AutoShape 2545", etc.) deleted.

The problem is that I have two command buttons from the Control Toolbox on
the sheet so if I use the code below, the command buttons also get deleted.
Any idea on how I can select all shapes but the two command buttons (Names
"CommandButton1" and "CommandButton2")?

Sheets("Sheet1").Activate
Shapes.SelectAll
Selection.Delete

Thanks,

--
Robert

Thanks Joel! Since the IDs of the shapes will be different each time data is
pasted into the sheet (it's an input sheet), I changed your code slightly so
that if the name of the shape is different from both of the cmd buttons
(which I renamed) then the shape is deleted. It worked perfectly. Thanks
again!


Sheets("Sheet1").Activate

For Each shp In ActiveSheet.Shapes
If shp.Name < "cmdreturn" And shp.Name < "cmdsubdel" Then
shp.Delete
End If

Next shp

--
Robert


"Joel" wrote:

For Each shp In ActiveSheet.Shapes
If shp.ID = 2544 Then
shp.Delete
End If

Next shp


"robs3131" wrote:

Hi all,

I have an issue where data is copied and pasted from an email into a sheet
where the data must be pasted in as Paste All -- the issue is that there are
objects of some sort that get pasted in also. When the code executes, I want
to have the objects (which get pasted in with the names "AutoShape 2544",
"AutoShape 2545", etc.) deleted.

The problem is that I have two command buttons from the Control Toolbox on
the sheet so if I use the code below, the command buttons also get deleted.
Any idea on how I can select all shapes but the two command buttons (Names
"CommandButton1" and "CommandButton2")?

Sheets("Sheet1").Activate
Shapes.SelectAll
Selection.Delete

Thanks,

--
Robert