Excel crashing when closing workbook in VBA

[jbenfleming]

When I run this code, Excel will crash when it reaches the last line, i.e where I close "document2". Any thoughts? I've used similar code before and it has always worked.

Option Explicit


Sub DAILYSALES_END()
Dim rownum As Double


'save document1 at temporary location
    Application.DisplayAlerts = False
    document1.SaveAs "tempdoc4salesmacro.xlms"
    Application.DisplayAlerts = True
    Workbooks.Open ("file path for document2")
    Windows("tempdoc4salesmacro.xlms").Activate
    
'move sheet to new document, change sheet name
    Sheets("BLANK").Select
    Sheets("BLANK").Move Before:=Workbooks("document2").Sheets(1)
    Range("B2").Select
    ActiveSheet.Name = Format(Date, "ddmmm")
    ActiveWorkbook.Save
    
'clear out data from first document
    DoEvents
    Workbooks.Open ("document1")
    Range("B2").Select
    If Range("B2").Value <> "" And Range("B3").Value = "" Then
        Range("B2:J2").Clear
    ElseIf Range("B2").Value <> "" And Range("B3").Value <> "" Then
        Selection.End(xlDown).Select: rownum = ActiveCell.Row
        Range(Cells(2, 2), Cells(rownum, 10)).Clear
    End If
    
       Range("D2:D30").Select
    Selection.Style = "Currency"
    Range("H2:H30").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.00%"
    Range("B2:J47").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    
    Range("B2").Select
'save and close document1
    ActiveWorkbook.Save
    ActiveWorkbook.Close


'save and close document2
    ActiveWorkbook.Save
    ActiveWorkbook.Close



End Sub

 

[Jeffrey Mahoney]

I think you're closing the workbook where the macro resides before the macro is finished. It then tries to finish the macro and gives you an error.

It would be clearer if you set your Workbook references as in below. I don't understand the line marked in red

Sub DAILYSALES_END()
Dim rownum As Double
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WB3 As Workbook


'save document1 at temporary location
    Application.DisplayAlerts = False
    document1.SaveAs "tempdoc4salesmacro.xlms"
    Set WB1 = ActiveWorkbook
    Application.DisplayAlerts = True
    Workbooks.Open ("file path for document2")
    Set WB2 = ActiveWorkbook
    Windows("tempdoc4salesmacro.xlms").Activate
    
'move sheet to new document, change sheet name
    Sheets("BLANK").Select
    Sheets("BLANK").Move Before:=Workbooks("document2").Sheets(1)
    Range("B2").Select
    ActiveSheet.Name = Format(Date, "ddmmm")
    WB2.Save
    
    
'clear out data from first document
    DoEvents
    [COLOR=#ff0000]Workbooks.Open ("document1")[/COLOR]
    Range("B2").Select
    If Range("B2").Value <> "" And Range("B3").Value = "" Then
        Range("B2:J2").Clear
    ElseIf Range("B2").Value <> "" And Range("B3").Value <> "" Then
        Selection.End(xlDown).Select: rownum = ActiveCell.Row
        Range(Cells(2, 2), Cells(rownum, 10)).Clear
    End If
    
       Range("D2:D30").Select
    Selection.Style = "Currency"
    Range("H2:H30").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.00%"
    Range("B2:J47").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    
    Range("B2").Select
'save and close document2
    WB2.Close savechanges:=True




'save and close document1


    WB1.Close savechanges:=True






End Sub