Why does my script close the excel workbook? Please help.

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
Hi,

I have the following script, but when I run it it closes the workbook...for some odd reason. Please can anyone tell me why it is doing that and how to stop it from closing?
I want it to save but then tokeep the file/workbook open afterwards.

Sub Save_As()

Application.ScreenUpdating = False

Application.DisplayAlerts = True
' Save report as xlsx and csv format

Dim FIMV As Workbook, path As String

Set FIMV = ThisWorkbook

FIMV.Sheets("Summary").Buttons.Delete

path = "M:\Fixed Income\Fixed Income Pacer\Holding Position\As of " & Right(FIMV.Sheets("Summary").Cells(2, 13), 6)

ActiveWorkbook.SaveAs path & ".xlsx", FileFormat:=xlOpenXMLWorkbook

FIMV.Sheets("CADBONDS").Activate

FIMV.Sheets("CADBONDS").Columns("I:L").Delete

ActiveWorkbook.SaveAs "M:\Fixed Income\Fixed Income Pacer\Holding Position\universe.csv", FileFormat:=xlCSV

FIMV.Sheets("Longbond").Activate

FIMV.Sheets("Longbond").Columns("I:L").Delete

ActiveWorkbook.SaveAs "M:\Fixed Income\Fixed Income Pacer\Holding Position\long.csv", FileFormat:=xlCSV

Application.DisplayAlerts = True

FIMV.Save


End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,419
Office Version
  1. 365
Platform
  1. Windows
Go to the VBA window. Right click on 'This Workbook' and then view code. Check for Workbook_AfterSave
 

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
Go to the VBA window. Right click on 'This Workbook' and then view code. Check for Workbook_AfterSave
Thanks for the prompt response, however it did not find anything.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,419
Office Version
  1. 365
Platform
  1. Windows
First make sure you are looking in the correct workbook. Then click in the border to the left of the line 'FIMV.Save' so the line goes brown and you get a brown circle in the border then run the code. Does it close now?
 

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows

ADVERTISEMENT

First make sure you are looking in the correct workbook. Then click in the border to the left of the line 'FIMV.Save' so the line goes brown and you get a brown circle in the border then run the code. Does it close now?
No it does not. Why does it close when I run by way of triggering a button but doesn't when I go into the VBA window and run it from there?
 

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
First make sure you are looking in the correct workbook. Then click in the border to the left of the line 'FIMV.Save' so the line goes brown and you get a brown circle in the border then run the code. Does it close now?
Oh I should mention, I have other Modules in this workbook that have a wb.close function at the end. However they are different modules and should not be triggered as they are run by a different macro button to sub Save as(). Could that be the issue?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,419
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok so you may wish to look what macro is run from the button click. It could run another macro, or several in turn.
 
Solution

HEzim

New Member
Joined
Mar 4, 2021
Messages
12
Office Version
  1. 365
  2. 2013
  3. 2011
Platform
  1. Windows
Ok so you may wish to look what macro is run from the button click. It could run another macro, or several in turn.
thank you, my last question - is there a way to reveal which macros are specifically triggered by the button?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,419
Office Version
  1. 365
Platform
  1. Windows
Depends on the button type. Right click it. Does it have 'assign macro'. If not go to the developer tab and turn on design mode, right click button and view code.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

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