ExcelBanter - Error when deleting sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Error when deleting sheets (https://www.excelbanter.com/excel-programming/333879-error-when-deleting-sheets.html)

John

Error when deleting sheets

when I run the following code... I get a 1004 error saying a can't delete the
worksheet I have specified. (something like a bad delete method).

any ideas?

Sheets("DATA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Agencies").Select
ActiveWorkbook.Protect Password:="test", Structu=True, Windows:=False
sFilename = "S:\Inventory\" & "SNW Inventory " &
Format(Worksheets("Data").Range("g1").Value, "yyyy-mm-dd")
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
End If
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Unprotect Password:="test"
Application.DisplayAlerts = False
Sheets("Muni's").Select
ThisWorkbook.Sheets("Muni's").Delete
Sheets("Agencies").Select
Application.DisplayAlerts = True

End Sub

Jim Thomlinson[_4_]

Error when deleting sheets

Try changing
ThisWorkbook.Sheets("Muni's").Delete
to
Activesheet.Delete
--
HTH...

Jim Thomlinson

"John" wrote:

when I run the following code... I get a 1004 error saying a can't delete the
worksheet I have specified. (something like a bad delete method).

any ideas?

Sheets("DATA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Agencies").Select
ActiveWorkbook.Protect Password:="test", Structu=True, Windows:=False
sFilename = "S:\Inventory\" & "SNW Inventory " &
Format(Worksheets("Data").Range("g1").Value, "yyyy-mm-dd")
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
End If
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Unprotect Password:="test"
Application.DisplayAlerts = False
Sheets("Muni's").Select
ThisWorkbook.Sheets("Muni's").Delete
Sheets("Agencies").Select
Application.DisplayAlerts = True

End Sub

Tom Ogilvy

Error when deleting sheets

You protected the structure of the workbook, which means you can't delete
sheets. You would have to unprotect the structure of the workbook before
deleting the sheet.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
when I run the following code... I get a 1004 error saying a can't delete
the
worksheet I have specified. (something like a bad delete method).

any ideas?

Sheets("DATA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Agencies").Select
ActiveWorkbook.Protect Password:="test", Structu=True,
Windows:=False
sFilename = "S:\Inventory\" & "SNW Inventory " &
Format(Worksheets("Data").Range("g1").Value, "yyyy-mm-dd")
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
End If
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Unprotect Password:="test"
Application.DisplayAlerts = False
Sheets("Muni's").Select
ThisWorkbook.Sheets("Muni's").Delete
Sheets("Agencies").Select
Application.DisplayAlerts = True

End Sub

John

Error when deleting sheets

That didn't do it... it says

Delete Method of Worsheet Class failed...

Is there a way to access the menu and go to edit, then delete worksheet?

"Jim Thomlinson" wrote:

Try changing
ThisWorkbook.Sheets("Muni's").Delete
to
Activesheet.Delete
--
HTH...

Jim Thomlinson

"John" wrote:

when I run the following code... I get a 1004 error saying a can't delete the
worksheet I have specified. (something like a bad delete method).

any ideas?

Sheets("DATA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Agencies").Select
ActiveWorkbook.Protect Password:="test", Structu=True, Windows:=False
sFilename = "S:\Inventory\" & "SNW Inventory " &
Format(Worksheets("Data").Range("g1").Value, "yyyy-mm-dd")
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
End If
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Unprotect Password:="test"
Application.DisplayAlerts = False
Sheets("Muni's").Select
ThisWorkbook.Sheets("Muni's").Delete
Sheets("Agencies").Select
Application.DisplayAlerts = True

End Sub

Dave Peterson[_5_]

Error when deleting sheets

You protected the workbook:
ActiveWorkbook.Protect
But you unprotected the worksheet:
ActiveSheet.Unprotect Password:="test"

Try unprotecting the workbook instead.

John wrote:

when I run the following code... I get a 1004 error saying a can't delete the
worksheet I have specified. (something like a bad delete method).

any ideas?

Sheets("DATA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Agencies").Select
ActiveWorkbook.Protect Password:="test", Structu=True, Windows:=False
sFilename = "S:\Inventory\" & "SNW Inventory " &
Format(Worksheets("Data").Range("g1").Value, "yyyy-mm-dd")
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
End If
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Unprotect Password:="test"
Application.DisplayAlerts = False
Sheets("Muni's").Select
ThisWorkbook.Sheets("Muni's").Delete
Sheets("Agencies").Select
Application.DisplayAlerts = True

End Sub

--

Dave Peterson

John

Error when deleting sheets

wow, I feel a little silly... thanks

"Dave Peterson" wrote:

You protected the workbook:
ActiveWorkbook.Protect
But you unprotected the worksheet:
ActiveSheet.Unprotect Password:="test"

Try unprotecting the workbook instead.

John wrote:

when I run the following code... I get a 1004 error saying a can't delete the
worksheet I have specified. (something like a bad delete method).

any ideas?

Sheets("DATA").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Agencies").Select
ActiveWorkbook.Protect Password:="test", Structu=True, Windows:=False
sFilename = "S:\Inventory\" & "SNW Inventory " &
Format(Worksheets("Data").Range("g1").Value, "yyyy-mm-dd")
ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
End If
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveSheet.Unprotect Password:="test"
Application.DisplayAlerts = False
Sheets("Muni's").Select
ThisWorkbook.Sheets("Muni's").Delete
Sheets("Agencies").Select
Application.DisplayAlerts = True

End Sub

--

Dave Peterson

All times are GMT +1. The time now is 12:02 AM.