Microsoft error closing Excel

Posted on 2012-08-14
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
    LVL 24

    Expert Comment

    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

    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

    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

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

    Accepted Solution

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now