Vba to save and close workbook leaves personal workbook open

[jondavis1987]

So if I run this when it's the only workbook open it will save the changes and close that workbook. But the personal macro workbook will now be showing. I'd like it where if I execute this macro it will save the workbook and close out of it and the personal macro book if the active workbook is the only one open. Having just done it again I realize it's not the personal macro book. It's just a blank excel thing waiting for me to either close it or open a new workbook. I would still like this closed if possible when the active workbook is the only workbook open

Sub Log_Changes()

Dim lastRow As Long

lastRow = Sheets("Superpave Template").Cells(Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lastRow).NumberFormat = "mm/dd/yyyy"
Range("C" & lastRow).NumberFormat = "0%"
Range("D" & lastRow).NumberFormat = "0%"
Range("E" & lastRow).NumberFormat = "0%"
Range("F" & lastRow).NumberFormat = "0%"
Range("G" & lastRow).NumberFormat = "0%"
Range("H" & lastRow).NumberFormat = "0%"

ActiveSheet.Range("B39").Copy
ActiveSheet.Range("B" & lastRow).PasteSpecial xlPasteValues
ActiveSheet.Range("C5:H5").Copy
ActiveSheet.Range("C" & lastRow).PasteSpecial xlPasteValues
       ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[AlphaFrog]

This will close any workbook named Book* which are usually your blank unsaved workbooks.

ActiveSheet.Range("C" & LastRow).PasteSpecial xlPasteValues
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        If wb.Name Like "Book*" Then wb.Close SaveChanges:=False
    Next
    ActiveWorkbook.Close SaveChanges:=True

 

[jondavis1987]

Thank you for the help. I tried it and it didn't work. I figured out the problem and a code that kind of does what I want. When I used the code before Excel would stay open with no workbooks open. So I went to the code below.

Sub Log_Changes()

Dim lastRow As Long

lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lastRow).NumberFormat = "mm/dd/yyyy"
Range("C" & lastRow).NumberFormat = "0%"
Range("D" & lastRow).NumberFormat = "0%"
Range("E" & lastRow).NumberFormat = "0%"
Range("F" & lastRow).NumberFormat = "0%"
Range("G" & lastRow).NumberFormat = "0%"
Range("H" & lastRow).NumberFormat = "0%"

ActiveSheet.Range("B39").Copy
ActiveSheet.Range("B" & lastRow).PasteSpecial xlPasteValues
ActiveSheet.Range("C5:H5").Copy
ActiveSheet.Range("C" & lastRow).PasteSpecial xlPasteValues

 If IsEmpty(Range("H3").Value) = True Then
Range("H" & lastRow) = ""
End If

 If IsEmpty(Range("G3").Value) = True Then
Range("G" & lastRow) = ""
End If
 
 If IsEmpty(Range("F3").Value) = True Then
Range("F" & lastRow) = ""
End If

 If IsEmpty(Range("E3").Value) = True Then
Range("E" & lastRow) = ""
End If

 If IsEmpty(Range("D3").Value) = True Then
Range("D" & lastRow) = ""
End If

 If IsEmpty(Range("C3").Value) = True Then
Range("C" & lastRow) = ""
End If

Dim wb As Workbook
Dim win As Window
Dim i As Integer
i = 0
For Each win In Application.Windows
    If win.Visible = True Then
        i = i + 1
    End If
Next win
If i = 1 Then
    Application.Quit SaveChanges:=True
Else
    ThisWorkbook.Close SaveChanges:=True
End If
End Sub

 

[jondavis1987]

Well, hadn't tried it with the save changes added to the application quit or the workbook close. It works without those being there but still asks me if i want to save instead of automatically saving. With the Save changes in there it says wrong number of arguments or invalid property assignment and highlights the application.quit