Save as macro problem

rdsouza

New Member
Joined
Mar 20, 2004
Messages
31
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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

Well-known Member
Joined
Jun 17, 2004
Messages
669
also, application.displayalerts = false might help, but dont forget to turn them back on at the end.
 

rdsouza

New Member
Joined
Mar 20, 2004
Messages
31
Is there any way that I can specify the active sheet name as the file name
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
How about this:
Code:
Dim i As String
i = ActiveSheet.Name
ActiveWorkbook.SaveAs Filename:=i & ".xls"
Hope it helps.
 

Forum statistics

Threads
1,147,620
Messages
5,742,177
Members
423,708
Latest member
vagosh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top