Code to delete shapes in copied sheets

  • Thread starter Thread starter nelly
  • Start date Start date
N

nelly

I have a form/macro that allows users to select sheets from a workbook and
then copies these to a new workbook. Some of these sheets have shapes which
need deleting. I am having trouble with the following code so need help. The
new workbook does not yet have a name as the user saves it as what ever they
need it to be.

For i = 1 To Sheets.count
ActiveWorkbook.Sheets(i).Activate
Set myDocument = ActiveWorkbook.Sheets(i).Name
myDocument.Shapes.SelectAll
Selection.Delete
Next i

Thanks in advance
Nelly
 
there are obviously better ways, but this is quick & simple

Sub shapekiller()

Dim ws As Worksheet
Dim sh As Shape

For Each ws In Worksheets
For Each sh In ws.Shapes
sh.Delete
Next
Next

End Sub
 
Nelly,

Deleting 'ALL' shapes can be dangerous, you may end up deleting things you
don't want to but if that's what you want try this but note it will delete
all shapes so try it on a test workbook

Dim sh As Shape
For i = 1 To Worksheets.Count
For Each sh In ActiveWorkbook.Sheets(i).Shapes
sh.Delete
Next
Next

Mike
 
Back
Top