Microsoft error closing Excel

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-08-16
I have attached a macro that I have written that is causing Excel to close down.
I can not attach the relevant files.
The macro deletes the previous output workbooks then  loops through a list of variables, one row for each set of variables (scenarios).
With each run the model applies the variables resulting in a refreshed output sheet which is in the same workbook. The model then creates a new worksheet for the scenario that has just been run. The macro copies the data from the refreshed output sheet into the new worksheet and renames the new worksheet the same as the scenario
Next  it creates a new workbook, moves the new scenario worksheet into the new workbook, names the workbook the same as the worksheet and saves in the specified folder. The whole process is repeated for each scenario with each stage being flagged as completed
The macro was built in sections to test on sample data and worked fine. However when the whole macro is run it produces the new workbooks but at the end of the macro when the mouse button is clicked I get the message
 “Microsoft Office Excel has encountered a problem a needs to close”.          
It also closes the workbook and creates a version. Whilst the output files are fine all record of the stages being completed is lost (necessary for Audit)
This happens every time I try to run the macro I have tried changing bits of code but to no avail.
 Can anyone help
Question by:DAVID131
  • 3
  • 2
LVL 24

Expert Comment

ID: 38293682
OK, two issues that I can see...

1) Window naming... if in windows folder view properties you have the option "hide extensions for known filetypes" ticked, your window names will not have the extensions.
So for example...
Windows("SCENARIO PRICING.xlsm").Activate

Open in new window

may need to be...
Windows("SCENARIO PRICING").Activate

Open in new window

2) Selecting rather than direct referencing
Rather than selecting the sheet, then the range, then adding the formula (as below)
Sheets("Primo checklist & macros").Select
ActiveCell.FormulaR1C1 = "This macro has been run"

Open in new window

you may be better if you dim the workbooks / worksheets and then not select...
dim ws as worksheet
set ws = Sheets("Primo checklist & macros")
ws.Range("F12") = "This macro has been run"

Open in new window

these are just two possible changes to address.

Author Comment

ID: 38297552
Thank you for your quick reply
The suggestions did not alleviate the problem.
However I revisted the code line-by-line and found that by inserting the middle line below that the problem stopped

Sheets.Add after:=Sheets(numbersheets)
ActiveSheet.Name = Sheet9.[F2].Value

LVL 24

Expert Comment

ID: 38297605
Again using Select can be volatile and slow down the code considerably...

Dim wsNew as Worksheet

Set wsNew = Sheets.Add after:=Sheets(numbersheets)
wsNew.Name = Sheet9.[F2].Value

Open in new window

then work with wsNew rather than activesheet etc.

Author Comment

ID: 38299294
I tried this but got a syntax error
see attached
LVL 24

Accepted Solution

Steve earned 2000 total points
ID: 38299454
Sorry, didn't test the code (thought It was too simple to get wrong, daft old me)

the Set wsNew should have the After in brackets:
Set wsNew = Sheets.Add(after:=Sheets(numbersheets))

Open in new window


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question