Microsoft error closing Excel

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
Who is Participating?
SteveConnect With a Mentor Commented:
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

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.
DAVID131Author Commented:
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

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.
DAVID131Author Commented:
I tried this but got a syntax error
see attached
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.