VBA code

[Leisuregroove]

Hello,

I wonder if anyone can help me. I am a complete novice at VBA so please excuse me.

I have some code, which opens another work book, "saves as" in a different folder then deletes the file.
This is done to keep the links on a linked document when moving the linked documents.

I want to know how do I automatically save the last document which is opened instead of getting a prompt to save changes made.

here's my code.

' Macro1 Macro
'
Sub Open_Acc1_CANCEL()
'
Workbooks.Open Filename:= _
"W:\Accumulative\Accumulative 2018-2019\SSC8 - Health & Fitness.xlsm", _
UpdateLinks:=0
ActiveWorkbook.Save
Windows("HAF959 - Mental Health"). _
Activate
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit


Dim strWbKill As String
With ThisWorkbook
strWbKill = .FullName
.SaveAs "W:\Cancelled courses\HAF959 - Mental Health.xlsm"
End With
KILL strWbKill
ActiveWorkbook.Close


End Sub


When all this code is run, there is one last document which Is W:\Accumulative\Accumulative 2018-2019\SSC8 - Health & Fitness.xlsm . I am prompted to save but I need the code to save automatically.

pulling my hair out lol....

Best,

LG

 

[KennyGreens]

Hi there, one way to get rid of that prompt to save is by using this:

Applicaion.ScreenUpating = False
Applicaton.DisplayAlerts = False

ScreenUpdating will allow the macro to run, but will not show you what the macro is doing until it is complete. The DisplayAlerts will simply block any alerts/prompts that would normally pop up when doing each step manually. Don't forget to set these to true again at the very end of your macro.

Edit: I should mention that with the SaveAs method, with DisplayAlerts being set to false, it will automatically select yes for SaveAs.

 

[Leisuregroove]

Hi there, one way to get rid of that prompt to save is by using this:

Applicaion.ScreenUpating = False
Applicaton.DisplayAlerts = False

ScreenUpdating will allow the macro to run, but will not show you what the macro is doing until it is complete. The DisplayAlerts will simply block any alerts/prompts that would normally pop up when doing each step manually. Don't forget to set these to true again at the very end of your macro.

Edit: I should mention that with the SaveAs method, with DisplayAlerts being set to false, it will automatically select yes for SaveAs.

Do you know where I should add it? It keeps saying object needed..

 

[KennyGreens]

Do you know where I should add it? It keeps saying object needed..

Declaring those as false should be done at the very start of the macro.

 

[RoryA]

There's a typo in the first line - it should be:

Application.ScreenUpating = False

 

[Leisuregroove]

There's a typo in the first line - it should be:

Application.ScreenUpating = False

It says object doesn't support this property or method.

here's the code:

' Macro1 Macro
'
Sub Open_Acc1_CANCEL()
'
Application.ScreenUpating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:= _
"U:\WIP\Stuff\AREA8B\SSC8B - Travel & Tourism\SSC8B - Travel & Tourism.xlsm", _
UpdateLinks:=0
ActiveWorkbook.Save

Windows("TAT900 - Level 1 Award in Travel & Tourism - 03.10.2018 - FULL.xlsm"). _
Activate
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit


Dim strWbKill As String
With ThisWorkbook
strWbKill = .FullName
.SaveAs "U:\TAT900 - Level 1 Award in Travel & Tourism - 03.10.2018 - FULL.xlsm"
End With
KILL strWbKill
ActiveWorkbook.Close



End Sub

 

[RoryA]

My mistake - there were actually two typos!

Application.ScreenUpdating = False

 

[KennyGreens]

My mistake - there were actually two typos!

Application.ScreenUpdating = False

Thanks for correcting this! For some reason the quick reply section does not agree well with my browser and lags often, missing letters that I type