Excel macro needs a tweak to auto save

Posted on 2009-04-15
Last Modified: 2012-05-06
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

Question by:MaidToOrder
    LVL 92

    Accepted Solution

    Presumably you want to save the changes, so change:



                    mybook.Close True
    LVL 80

    Assisted Solution

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    This collection of functions covers all the normal rounding methods of just about any numeric value.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now