DisplayAlerts Error

[justzach]

first of all i would like to thank EVERYONE on this forum for all of th
great tips and help you have provided a newbie like myself. over th
past week i have learned a great deal of information about excel and V
which has in turned helped me build an excel "program" that i
incredibly helpful for my job.

now my problem...
i have seen post after post describing how to turn off alerts in V
code so that for example the "save as" dialog box will not appear. th
code i am using is:
Application.DisplayAlerts = False
this code works great in one part of my code, but in the followin
snippet it does nothing at all... any ideas???

snippet:
Sheets(Array("Courses", "Extra")).Select
Sheets("Extra").Activate
Sheets(Array("Courses", "Extra")).Copy
Application.DisplayAlerts = False
Application.Dialogs(xlDialogSaveAs).Show arg1:="c:\uga_co\Summer Dat
Sheet.xls"
Application.DisplayAlerts = True
Windows("Summer Data Sheet.xls").Activate


the code does nothing, i still am faced with the "save as" box. i hav
tried removing the "true" statement alltogether, and it still prompt
me for the alert. the only part of my code that this works properly i
in the following snippet:

snippet:
Application.DisplayAlerts = False
Sheets("Room Cap").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Instructor").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Courses").Select
Application.DisplayAlerts = True


please advise and educate me!
thank you very much in advance

 

[Tom Ogilvy]

That isn't how you save a fill unless you want to do it manually which
doesn't appear to be the case:

Sheets(Array("Courses", "Extra")).Select
Sheets("Extra").Activate
Sheets(Array("Courses", "Extra")).Copy
ActiveWorkbook.SaveAs "c:\uga_co\Summer Data Sheet.xls"
' Next line isn't necessary
'Windows("Summer Data Sheet.xls").Activate

 

[Wouter]

Hi,

It looks to me taht you want to create a new workbook with only the two sheets.
Try this:

' First remove exsisting file
If Len(Dir("c:\uga_co\Summer Data Sheet.xls", vbNormal)) > 0 then
Kill "c:\uga_co\Summer Data Sheet.xls"
End If

Sheets(Array("Courses", "Extra")).Select
Sheets(Array("Courses", "Extra")).Copy
ActiveWorkbook.SaveAs Filename:="c:\uga_co\Summer Data Sheet.xls", _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

GoodLuck,

Wouter

 

[justzach]

thanks guys! between the two of you i have the code i now need