2 Small Macro problems.

Hi all,

Problem 1:

I am trying to delete a sheet within a workbook, as part of macro. Here's the code:

Sub DeleteInstructions()
'
' DeleteInstructions Macro
' Macro recorded 3/13/2007 by Gary Beckwith
'

'
Sheets("INSTRUCTIONS").Select
ActiveWindow.SelectedSheets.Delete
Sheets("DATA").Select
End Sub

When I run it, I get the prompt "Data may exist in the sheet for deletion. To permanently delete the data, press delete." I have to click OK. How can I get the macro to do this for me

Problem 2:

I have just sorted and then copied text from another file into this one. I want to go back to that file I just sorted and save and close it. Here's the code:

Sub CloseOtherFile()
'
' CloseOtherFile Macro
' Macro recorded 3/13/2007 by Gary Beckwith

ActiveWindow.ActivateNext
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

It goes to close it, but then asks if I want to save it. How can I get the macro to do this for me

Thank you,

Gary

Hi,

For problem 1, you can set the Application.DisplayAlerts property to False. It's prudent to set it back to True afterwards however, or Excel could need to warn you of something and you wouldn't know about it.

Sub DeleteInstructions()
'
' DeleteInstructions Macro
' Macro recorded 3/13/2007 by Gary Beckwith
'

    Application.DisplayAlerts = False

    Sheets("INSTRUCTIONS").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("DATA").Select

    Application.DisplayAlerts = True

End Sub

For problem 2, the Close method has an optional SaveChanges parameter, which if you set to False, should eliminate the save prompt dialog. Alternatively, you could completely ommit the Save method and just call the Close method and pass True to the SaveChanges parameter.

Thank you.  Your solution to problem 1 works perfectly.  I'm still struggling with problem 2. I may just leave it in the macro.  It works; it's just that the operator has the click "OK" to save changes.

For problem 2, I have copied you code and tested it on 2 existing workbooks I had, and two new workbooks that have never previously been saved. Both times it worked OK, without prompting me at all.

Odd

Perhaps someone else on here with more experience may know how to get around the problem.

Hi Keith. The problem is probably me; I don't know how to set the "save changes" parameters.

Gary

Try this:

Sub CloseOtherFile()
'
' CloseOtherFile Macro
' Macro recorded 3/13/2007 by Gary Beckwith

ActiveWindow.ActivateNext
ActiveWindow.Close SaveChanges:=True
End Sub

It works perfectly - thanks very much!

Gary