Save as macro problem

[rdsouza]

I have the following code in a macro

Workbooks.OpenText Filename:=Application.GetOpenFilename
Columns("A:D").Select
Selection.Delete Shift:=xlToLeft
Columns("B:AF").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Range("A1:A50000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"B1"), Unique:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.SaveAs
ActiveWorkbook.Close
Application.Quit
after executing the macro excel asks to save the file. Is there any way to automate this so that excel will answer yes to the save as question.
Thanks
Rodney

 

[HalfAce]

To answer your specific question, you can change this line:
ActiveWorkbook.Close
To:
ActiveWorkbook.Close True

However, while cleaning up the code a bit to get rid of all those selections (you generally don't need to select a range to work with it, and it's faster if you don't), I noticed you're using "ActiveWorkbook.SaveAs, but then there's no name to save it as... (?) If it's given a name to save as, then you can do away with the ActiveWorkbook.Close line altogether as you're closing out the application anyway. :wink:

See if this works for you and is maybe a bit quicker.

Workbooks.OpenText Filename:=Application.GetOpenFilename
Columns("A:D").Delete Shift:=xlToLeft
Columns("B:AF").Delete Shift:=xlToLeft
Range("A1:A50000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True
Columns("A:A").Delete Shift:=xlToLeft
Range("A1").Delete Shift:=xlUp
ActiveWorkbook.SaveAs 'SaveAs what???
Application.Quit

Hope it helps,
Dan

 

[tmischler]

also, application.displayalerts = false might help, but dont forget to turn them back on at the end.

 

[rdsouza]

Is there any way that I can specify the active sheet name as the file name

 

[HalfAce]

How about this:

Dim i As String
i = ActiveSheet.Name
ActiveWorkbook.SaveAs Filename:=i & ".xls"

Hope it helps.