Excel macro needs a tweak to auto save

I use this macro to collect data from a range on a number of sheets. I'm pretty happy with it, except that it asks if I want to save the changes to each sheet, so I have to hold the enter key while it runs. Is there a way to have the macro automatically select yes?
Sub demographic_list()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim rnum As Long
    Dim i As Long
    Dim a As Long
    Application.ScreenUpdating = False
    With Application.FileSearch
        .LookIn = "C:\_MTO\Customers"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            rnum = 1
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                Set sourceRange = mybook.Worksheets(1).Range("fa2:fz2")
                a = sourceRange.Rows.Count
                With sourceRange
                    Set destrange = basebook.Worksheets(1).Cells(rnum, 1). _
                                    Resize(.Rows.Count, .Columns.Count)
                End With
                destrange.Value = sourceRange.Value
                rnum = i * a + 1
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Presumably you want to save the changes, so change:



                mybook.Close True
byundtConnect With a Mentor Commented:
I assume that you are complaining about statement 27. You might consider the following:
mybook.Close SaveChanges:=True

The SaveChanges parameter is optional. If you omit it, then you get the dialog asking to save changes when you close the workbook. When the parameter is True, then the workbook is saved without asking, then closed. When it is false, the workbook is closed without saving.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.