Thread: Excel shutting down during save

Okay, I've got a strange problem. The macro below WAS (yesterday) working well to save the active workbook to a specific location with a specific name. Now, Excel shuts down when it hits that bit of code. Any idea what would be causing it? BTW, it tries to save to the location; there's an unknown type of fill deposited there, with a name like "7A08C000". If I put an XLS extension on it it says the file format is not valid.

If I change "ActiveWorkbook" to "TwoB" I get the same result. But, if I make a different workbook the active workbook, the macro works.

Sub Save()
Set TwoB = Workbooks("2BDeleted.xls")
        Application.DisplayAlerts = False
        Dim MO As Variant, HO As Variant
        Set MO = TwoB.Sheets("Lookups").Range("H1")
        Set HO = TwoB.Sheets("Lookups").Range("M1")
        Stop
        ActiveWorkbook.SaveAs FileName:= _
            "\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\CPS\MFR Projections\2011\Current\" & HO & " MFR Projection for " & MO & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False

        Application.DisplayAlerts = True
End Sub

EDIT: I edited the post to attach a copy of the Lookups sheet.

Two thoughts spring to mind:
1. Use HO.Value and MO.Value
2. Don't call your sub Save as that is an Excel method

Romper,

Thanks for the advice, but no go. I tried putting .value after MO and HO, and still got the same reaction. "Save" was just a temporary macro, not the full routine, so I changed the name to "SaveIt". As a separate routine, if I just run it over and over, sometimes it will work, sometimes it won't. As part of the larger routine, it fails every time, but catastrophically, by shutting down Excel. I've pasted the larger routine below; it's a lot to go through, but maybe it will give you a clue as to what's happening.

Sub Finish()
   'Restore the play pivot to its former glory
   Run "Killbutts"
   
   'Now back to the magic
   Set TwoB = Workbooks("2BDeleted.xls")
   'Get the splash page back
   Workbooks("MFR Projection Tool.xls").Sheets("Sheet1").Activate
   
   With Application
        .ScreenUpdating = False
        .EnableEvents = False
        
        TwoB.Sheets("Play").Activate
                
        'Copy the pivot to our Worksheet page as values and formats
        Set PT = ActiveSheet.PivotTables(1)
        PT.TableRange1.Copy
        TwoB.Sheets("Worksheet").Activate
        
        With TwoB.Sheets("Worksheet").Range("A1")
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
            Range("A1").EntireRow.Delete
            
            'Refigure our last row
                LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0).Row
            
            'Fill in the blanks in columns A through B with the value above
                Range("A3:B" & LastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
            'Columns("A:B").Value = Columns("A:D").Value
                Columns("A:B").Copy
                Columns("A:B").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            
            'Create the lookup column
            Range("A1").EntireColumn.Insert
            'Plug in the formula
            Range("A2:A" & LastRow).FormulaR1C1 = "=RC[1]&RC[2]&RC[3]"
        End With 'Done with the Worksheets sheet
        
        'Now let's bring in the values from the Play pivot to our projections
        TwoB.Sheets("MFR Adjustments").Activate
        
        With TwoB.Sheets("MFR Adjustments")
            'Refigure our last row
            LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0).Row
            Range("E1").FormulaR1C1 = "Current MFR Projection"
            Range("E2:E" & LastRow).FormulaR1C1 = _
                "=IF(ISNA(VLOOKUP(RC[-4]&RC[-3]&RC[-2],Worksheet!C[-4]:C[3],8,FALSE)),0,VLOOKUP(RC[-4]&RC[-3]&RC[-2],Worksheet!C[-4]:C[3],8,FALSE))"
            Range("F1").FormulaR1C1 = "MFR Adjustments"
            Range("F2:F" & LastRow).FormulaR1C1 = "=RC[-2]-RC[-1]"
              
            'Find last column
            LastCol = Range("IV5").End(xlToLeft).Column
            With Application.WorksheetFunction
            
            'Place column totals in row after current last row
                For iCol = 5 To LastCol 'Starting in column E
                    Cells(LastRow + 1, iCol) = .Sum(Range(Cells(1, iCol), Cells(LastRow, iCol)))
                Next iCol
            End With
            Columns("A:F").EntireColumn.AutoFit
            Columns("D:F").Style = "Comma"
        End With 'Done with the MFR Adjustments sheet
       
        'Now let's save this puppy where it needs to be
        Application.DisplayAlerts = False
        Dim MO As Variant, HO As Variant
        Set MO = TwoB.Sheets("Lookups").Range("H1")
        Set HO = TwoB.Sheets("Lookups").Range("M1")
        
        
        Run "SaveIt"
        
'        TwoB.SaveAs FileName:= _
'            "\\12AUST1001FS01\SHARE10011\Budget\SOBUDGET\CPS\MFR Projections\2011\Current\" & HO.Value & " MFR Projection for " & MO.Value & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
'            ReadOnlyRecommended:=False, CreateBackup:=False
        
        'Clean up
        Run "Delete2BDeleted"
        'Start showing if there are any problems
        'Application.DisplayAlerts = True
    
        'Let Allison know it's ready
        Run "Sendmail"
        
        'Now we save it to our desktop
        Application.DisplayAlerts = False
        Dim DTAddress As String
        Dim WshShell As Object
        Set WshShell = CreateObject("WScript.Shell")
        DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
        ActiveWorkbook.SaveAs DTAddress & ActiveWorkbook.Name
        Application.DisplayAlerts = True
        
        'Return the focus to excel
        AppActivate "Microsoft Excel"
        
        MsgBox "The projection file has been saved to your desktop and to" & vbCrLf & vbCrLf & _
                "S:\Budget\SOBUDGET\CPS\MFR Projections\2011\Current" & vbCrLf & vbCrLf & _
                "Please review to identify variances and items of concern."
                
        Workbooks("MFR Projection Tool.xls").Close False 'We're done.
 
    End With 'this turns screen updating back on
End Sub

Sub SaveIT()
Set TwoB = Workbooks("2BDeleted.xls")
        'Now let's save this puppy where it needs to be
        Application.DisplayAlerts = False
        Dim MO As Variant, HO As Variant
        Set MO = TwoB.Sheets("Lookups").Range("H1")
        Set HO = TwoB.Sheets("Lookups").Range("M1")
       
        ActiveWorkbook.SaveAs HO & " MFR Projection for " & MO & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False


        Application.DisplayAlerts = True
End Sub

A couple of immediate questions/observations:
1. Why are you using Run rather than calling the code directly?
2. There's a lot of unnecessary selecting, activating and unqualified Range statements in there.
3. What are the values of HO and MO?

1. I mean:

Killbutts

rather than:

Run "Killbutts"

2. For example here:

Range("A3:B" & LastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

you would be better off explicitly stating which sheet is being used. The odd thing is that sometimes you do it and others you don't - such as in this part:

        With TwoB.Sheets("Worksheet").Range("A1")
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
            Range("A1").EntireRow.Delete
            
            'Refigure our last row
                LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0).Row

where you have two fully qualified Range references, one unqualified one, then one qualified only with Activesheet.

3. That doesn't tell me what the values are.

If the code is all in the same workbook, you don't use Run, regardless of which workbook is being modified. If you do use Run, you should specify the workbook that the code is in.

1. Yes. You can also use

 Call Killbutts

but it is unnecessary.

Do you have any code in the BeforeSave event as a matter of interest?

Feel free - though if it's a lot of code I can't guarantee when I can look through it.