Saveas Exit & Close Without Prompts

[VinceF]

Good Morning Experts,
I'm having difficulty to get the workbook to saveas, exit and close without the prompts, I've tried several codes off the internet all to no avail.

I'd like for it to do the following...
Name the workbook to : Indianwood Quota.xlsm - saved in C:\Golf\Indianwood Quota - and close the workbook without any prompts

I'd like to add this to the code below.

Much appreciate your help.
VinceF
Excel 2016




Private Sub RESET_Click()

Dim warning
warning = MsgBox(Range("A1").Value & " 1. LOCK EARNINGS 2. ADJUST QUOTA'S 3. SAVE ROUND 4.RESET SHEET. This will reset the entire sheet. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "WARNING STOP COMPLETE THESE 4 ITEMS BEFORE PROCEEDING")
If warning = vbCancel Then Exit Sub

On Error Resume Next

Sheets("main").Range("B11:H50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("C5:C8").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("O11:W50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("Y11:AG50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("dD11:D50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("D3:L3").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("g11:H50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("L7").ClearContents
Sheets("main").Range("AK2:AL2").ClearContents
Sheets("main").Range("N3:O3").ClearContents
Sheets("main").Range("B2").Value = "SELECT COURSE"
Sheets("main").Range("B3").Value = "STR ADJ"
Sheets("main").Range("C3").Value = "SELECT GAME"
Sheets("main").Range("L5").Value = "STAFF"

MsgBox "THE FORM HAS BEEN RESET."


ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")


End Sub

 

[Anthony47]

So add the line marked ++++

ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
ActiveWorkbook.Close False          '++++ CLOSE WITHOUT SAVING

End Sub

Try...

 

[VinceF]

Thanks Anthony,

When I close it now I still get a prompt saying that the file already exist and if I want to replace it...I select YES and the workbook closes but leaves a blank workbook in its place.

Can we get rid of the prompt and blank workbook?

 

[Anthony47]

You say two things (I guess):
1) the command ActiveWorkbook.SaveAs asks if you want to replace an existing file that has the same name. Are you sure that the existing file can be replaced by the new one? If "Yes, I am sure and don't want to check" then add DisplayAlerts False/True around the SaveAs command:

Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
Application.DisplayAlerts = True

2) after closing the active workbook nothing is left
Well, when you execute ActiveWorkbook.SaveAs the original workbook is replaced by the new one; if the new one is closed then nothing will be left in the application
But this is what you asked:

I'd like for it to do the following...
Name the workbook to : Indianwood Quota.xlsm - saved in C:\Golf\Indianwood Quota - and close the workbook without any prompts

So if what you get is not what you want then you need to clarify which you objective; maybe we simply need to convert the SaveAs in SaveCopyAs (see Workbook.SaveCopyAs method (Excel) ), that will create the new file and leave the original file open with all the modifications saved in the new file

 

[VinceF]

I apologize for not being able to explain it clear enough.

When I trigger the code I'd like for it to automatically save & replace the file that's in C:\Golf\Indianwood Quota
Cell C1 is telling it what to name the file. The answer to your question of "Yes, I am sure and don't want to check" is yes, that is correct.
Once that is done I'm hoping that it will close the workbook completely.

I hope this helps and I do appreciate your assistance.
VinceF

I modified the code as you suggested and it gives me the prompt saying...file already exist and if I want to replace it...I select YES and the workbook now stays open.

ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
Application.DisplayAlerts = True

 

[Anthony47]

You are still asked to confirm the SaveAs because you forgot to set DisplayAlert=False before the SaveAs command:

Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
Application.DisplayAlerts = True

If after these lines the ActiveWorkbook.Close False is still present then the new generated file (the one created by the SaveAs command) should be closed. Did you remove it or is it still there?

 

[VinceF]

Here's what I have and this is what it does....
It saves the file and without a prompt as desired.
The workbook remains open to a blank page with everything greyed out and I have to manually close it.
I can live with this...but I was hoping that Excel would completely close down and that I'd be back at the Desktop.

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
ActiveWorkbook.Close True
Application.DisplayAlerts = False

 

[Anthony47]

Try the sequence I suggested:

Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
Application.DisplayAlerts = True
ActiveWorkbook.Close False          'CLOSE WITHOUT SAVING

This way:
-you will not be prompted to confirm, if the file already exists
-the final command close the workbook without saving it (again)
-in case there is a warning on the final (close) command you will be notified (but I don't expect any warning at this stage)
It is clear to me that the "Close" command kills the macro before executing the End Sub line, but that has never been a problem in my experience; if you receive an alert at that point maybe I'll change my mind

 

[VinceF]

I copied and pasted the VBA exactly as you wrote and it does everything that it's supposed to do but it's still leaving a blank greyed out workbook.
Is it possible that something else in the code is causing this...

Private Sub RESET_Click()

Dim warning
warning = MsgBox(Range("A1").Value & " 1. LOCK EARNINGS 2. ADJUST QUOTA'S 3. SAVE ROUND 4.RESET SHEET. This will reset the entire sheet. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "WARNING STOP - COMPLETE THESE 4 ITEMS BEFORE PROCEEDING")
If warning = vbCancel Then Exit Sub

On Error Resume Next

Sheets("main").Range("B11:H50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("C5:C8").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("O11:W50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("Y11:AG50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("dD11:D50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("D3:L3").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("g11:H50").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("L7").ClearContents
Sheets("main").Range("AK2:AL2").ClearContents
Sheets("main").Range("N3:O3").ClearContents
Sheets("main").Range("B2").Value = "SELECT COURSE"
Sheets("main").Range("B3").Value = "STR ADJ"
Sheets("main").Range("C3").Value = "SELECT GAME"
Sheets("main").Range("L5").Value = "STAFF"

MsgBox "THE FORM HAS BEEN RESET."

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ("C:\Golf\indianwood Quota\" & ThisWorkbook.Sheets("Main").Range("C1").Value & ".xlsm")
Application.DisplayAlerts = True
ActiveWorkbook.Close False 'CLOSE WITHOUT SAVING

End Sub

 

[Anthony47]

I copied and pasted the VBA exactly as you wrote and it does everything that it's supposed to do but it's still leaving a blank greyed out workbook.

I am not sure about what you mean for "blank greyed workbook"; can you take a screenshot that shows the Excel application and its content? After ActiveWorkbook.Close Excel will show a blank window (see the image), unless another workbook was and still stays active. So maybe what you see is normal....

Also, is there a reason for the line On Error Resume Next? If there is no reason for an error to be shown on the next lines then remove it; ora add On Error Resume Next just after the msgbox