• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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

2 Solutions
Patrick MatthewsCommented:
Presumably you want to save the changes, so change:



                mybook.Close True
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.


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now