Vba to save and close workbook leaves personal workbook open

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code:
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
This will close any workbook named Book* which are usually your blank unsaved workbooks.

Rich (BB code):
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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,477
Messages
5,624,981
Members
416,065
Latest member
meiravmeron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top