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

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

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

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

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

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