deleting sheets and shapes

[Gary Adamson]

I have a routine that should go through and delete the
shapes and other sheets in an Excel workbook.
The problem is that the button(shape) that is clicked to
perform this operation is not deleted. Also the sheet
doesn't want to delete as well.


Sub CreateSheet()
Dim Shp As Shape, i As Integer, FName, WName
FName = ActiveWorkbook.FullName
WName = ActiveWorkbook.Path & "\Week" & Range("A2")
ActiveWorkbook.Save
With thisworkbook.ActiveSheet
.Unprotect
On Error Resume Next
For Each Shp In .Shapes
Shp.Visible = False
Next Shp
Application.DisplayAlerts = False
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Current" Then .Sheets(i).Delete
Next i
Application.DisplayAlerts = False
.Protect
End With
ActiveWorkbook.SaveAs Filename:=WName
Workbooks.Open Filename:=FName
thisworkbook.Close
End Sub

 

[Dave Peterson]

Sometimes using "on error resume next" can mask errors that tell you what's
wrong.

I commented out that line and got yelled at the
If Sheets(i).Name <> "Current" Then .Sheets(i).Delete
line.

Since the preceding "with" statement is:
with thisworkbook.activesheet, you're essentially writing:
thisworkbook.activesheet.sheets(i).delete

and when you're deleting things, it's often easier to start at the highest
numbered item and work backwards. If you delete .sheets(1), then .sheets(2)
moves to .sheets(1). And you have to keep track of where that index currently
sits.

Option Explicit

Sub CreateSheet()
Dim Shp As Shape
Dim i As Long
Dim FName As String
Dim WName As String

FName = ActiveWorkbook.FullName
WName = ActiveWorkbook.Path & "\Week" & Range("A2")

ActiveWorkbook.Save
With ThisWorkbook
With .ActiveSheet
.Unprotect
'On Error Resume Next
For Each Shp In .Shapes
Shp.Visible = False
'shp.Delete '?????
Next Shp
End With
Application.DisplayAlerts = False
For i = .Sheets.Count To 1 Step -1
If LCase(.Sheets(i).Name) <> "current" Then
.Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = False
.Protect
End With
ActiveWorkbook.SaveAs Filename:=WName
Workbooks.Open Filename:=FName
ThisWorkbook.Close
End Sub

And I was confused about your use of Activeworkbook and ThisWorkbook. I'm not
sure if they referred to the same workbook or not.

But in any case, you may want to look at:
(someworkbook).savecopyas
in VBA's help.

It might make things a bit simpler.